Rows into columns without a case selection

Question

Rows into columns without a case selection

Felipe Saavedra

SSC-Addicted

Points: 477
More actions
January 26, 2005 at 9:45 am

#183952

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

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.

Felipe Saavedra SSC-Addicted Points: 477 More actions · Answer 2

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.

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 3

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.

Felipe Saavedra SSC-Addicted Points: 477 More actions · Answer 4

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

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 5

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
go

If 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.

Felipe Saavedra SSC-Addicted Points: 477 More actions · Answer 6

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