Rows into columns without a case selection
-
Hi, I'm newbee in here, and in the T-SQL, SQL server world.
I was learning a lot of it in the past weeks, and finally I've come with some results, but I can't show the desired view.. I mean
I Have a table like this
ID, Family, Freq
1 f1 23
1 f2 4
1 f20 2
2 f3 7
2 f5 4
2 f2 3
2 f7 3
2 f8 3
.... and so on
and I want
ID-----Famliy1-----Family2------Family3---------Family4 ...
----Family-Freq--Family--Freq--Family--Freq--Family--Freq...
1----f1-----23-----f2-----4------f20-----2----null---null...
2----f3-----7------f5-----4------f2------3-----f7------3 ...
I know how to conver rows into columns with a case selection criteria, ie with
MATH_OP( CASE WHEN selec=something THEN desired) AS name
but the problem it's I don't know the numbers of Families and for each id isn't the same number.
I saw an oracle solution to this, but I have to creates multiset, something that sql server 2k don't have.
If someone know, I'll really apreciate the answer.
Thx
-
Is there a max number of families? You could write self joins with that number of times if you know it. Otherwise you might need to create a temp table and load the data into that using multiple passes. I.e., load all family1s, then family2s, etc.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
mmm I can calculate the max number of families.. but I think I didn't explain very well my prbolem....
Well after a not to short procedure with querys an temporary tables, i obtain a table wich columns are (ID, Family, Freq)... there are a lot kind of families, and my last table it's a table that contains the top 3 families for each id. The problem is that for some ids, the max(freq) is 1, (ie there are X>3 rows for that id).
The original idea was like I said before, to present the data like this
ID-----Family1(the max)------Family2(2nd max)-------Family3(3rd max)
------Family ----Freq--------Family----Freq----------Family---Freq
1-------f1---------30----------f5-------10------------f3-------2
2-------f7---------70----------f1-------30---------------------
.....
etc, but the problem is there are more than 1 max or 2nd, 3rd
and thats why I present the problem like that... the other posible solution that I'm trying to use is this one
ID-----Family1(the max)------Family2(2nd max)-------Family3(3rd max)
------Family ----Freq--------Family----Freq----------Family---Freq
1-------f1---------30----------f5-------20------------f3-------2
1-------f1---------30----------f5-------20------------f7-------2
1-------f1---------30----------f5-------20------------f2-------2
2-------f7---------40----------f1-------10---------------------
2-------f5---------40----------f3-------10---------------------
.....
In which I repeat the values (or use null) to mix and present the data more close to the original idea.
-
Hi Felipe,
If you limit the result set to a fixed number of columns (according to examples, this will be 3), I think that there is a solution to the problem. Otherwise, you will end up with crosstab report involving dynamic tables (there is a suitable script for that in SSC script archive).
Now, I have questions:
1. Do you use SQL 2000 ?
2. How to treat ties (families with same frequency) ? In the first row of the desired result set of the third post in the tread you order families in the 3rd column in order f3, f7, f2, and in the second row you order them in order f1, f3. Is this a mistake in the example, and should they be ordered by family (f2, f3, f7), as they are in the second row ?
3. Is it acceptable to put comma-separated list of families in the result set, like this:
ID----Family1----Freq1----Family2----Freq2----Family3-----Freq3
1-----f1-----------30-----f5-----------20-----f2,f3,f7-----2
etc.
The other solution is to have this handler in the reporting tool, by concatenating values while report generation.
Regards,
Goce.
-
Related to your questions:
1 Yes I use SQL Server 2000
2 the f1, f2, fx are only names, in fact there are a varchar(255) with a long name, like "Steels and Other Stuff", and the sort order It's only related to the Frequency, a second order doesn't matter. I just have to group the results by its Frecuency.
3 Yes it could be acceptable, but I would be better like I present the results, because, I have to apply another search method after this, to select the best family, and to be able to do that, I have to look for in the first max col to that ids duplicated, (ie to that ids that have 2 or more first max values). Something like SELECT ID, Familiy1 FROM t HAVING count(1)>1.
However, yesterday, I change my procedure in order to did a mini rank for each id, and this way I'm able to use a MAX (CASE ...), but I'm still having problems because I want to select 2 cols for each case Family_Name and Freq.
This is my final select statement
SELECT rfqName, Repet, Freq,
max( CASE WHEN pos = 1
THEN Family
END ) AS Family1,
max( CASE WHEN pos = 2
THEN Family
END ) AS Family2,
max( CASE WHEN pos = 3
THEN Family
END ) AS Family3,
total
FROM #base2
GROUP BY rfqName, total
ORDER BY rfqName
where rfqName its the Id, and the idea its to give an output like this
ID----------------Family1------------...
----Repetitions--Frequncy--FamilyName
As you can see my first question was the same but with a little simplifications.
Well I'm loosing a lot of information with this, because the mini rank I did, begins in 1 and add 1 for each row for the same id, and it resets to 1 when the id changes. So it helps me only to my first post, where i can did a Case from 1 to the max(mini-rank) but I'm still unable to put 3 rows under FamilyX
In order to try to get the output like in my second post, I coud easy fix the mini-rank in order to have 1s for the max, 2s for the 2nd max, and 3 for the 3rd max in order to case 1,2,3
Well thx for the quick answers. And I hope you can have a complete picture of my problem with this
THX
-
Hi Felipe,
I came up with this procedure.
create procedure families_report as create table #tmp( id int not null, rank int not null, rank_with_ties int not null, Family varchar(10) not null, Freq int not null) create index ix_tmp on #tmp (rank) insert into #tmp select ID, (select count(*) from FF as tmp where (tmp.ID = FF.ID) AND ((tmp.Freq > FF.Freq) OR (tmp.Freq = FF.Freq) AND (tmp.Family FF.Freq) ) + 1 as rank_with_ties, Family, Freq from FF order by ID, Freq desc, Family asc select IDs.ID, f1.Family as Family1, f1.Freq as Freq1, f2.Family as Family2, f2.Freq as Freq2, f3.Family as Family3, f3.Freq as Freq3, f4.Family as Family4, f4.Freq as Freq4, f5.Family as Family5, f5.Freq as Freq5 from (select distinct ID from #tmp) as IDs left outer join (select ID, Family, Freq from #tmp where (rank = 1)) as f1 on (f1.ID = IDs.ID) left outer join (select ID, Family, Freq from #tmp where (rank = 2)) as f2 on (f2.ID = IDs.ID) left outer join (select ID, Family, Freq from #tmp where (rank = 3)) as f3 on (f3.ID = IDs.ID) left outer join (select ID, Family, Freq from #tmp where (rank = 4)) as f4 on (f4.ID = IDs.ID) left outer join (select ID, Family, Freq from #tmp where (rank = 5)) as f5 on (f5.ID = IDs.ID) order by IDs.ID drop table #tmp goIf you need to perform additional search, you can use the SQL that populates the temporary table. It contains a global rank column within the ID, that is used to identify the exact column in which the family appears. Also, it has a minor rank column (rank_with_ties), that ranks family groups. The temporary table is used to save execution time. My initial solution used UDF (that is why I asked you about SQL 2000), but it should be executed many times. This way, the SQL is executed only once. Selected row with two columns from each subquery (can be empty) is appended to the result set as two columns in the row for the corresponding ID.
You can add additional columns as needed, by adding a subquery after F5, but the number of columns in the result set is fixed.
I hope I understood the problem good enough, to help you.
Regards,
Goce.
-
Thx, a lot... that's what I want, and you show me some tricks with that query...
I thought that nobody will answer me at that point, but yesterday I was able to did the same but with a very dirty code with cursor and if statements and a lot of variables. Your code It's a lot easier and short. Thx
Very grateful
Fei
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply