TSQL , SQL/Server/2000 - Change a Verticle record to a Horizontal

[ideafixer]

I have sample data:

( * ) is not part of the primary key. Please see the below question.
-----------------------------------------------------------
ForeignKey Primary Key Description Code
1059645 *2.009423171 anothername1 thecode1
1059646 *2.009423171 anothername2 thecode2
1059647 *2.009423171 anothername3 thecode3
1061545 2.010670619 justaname anothercode
1061076 2.011974757 Name1 Code_a
1061077 2.011974757 Name2 Code_b
1061605 2.014092249 anothername anothercode
1060857 2.014271366 Name1 code_a
1060858 2.014271366 Name2 code_b
1061764 2.014436999 name misccode
1061488 2.014831398 name misccode
1061325 2.015730148 Name1 code_b
1061326 2.015730148 Name2 code_a
-----------------------------------------------------------

I have a child table storing eight codes/descriptions related to one master record. The master record could have 0 - 8 codes depending on the data imported. For reporting porposed, how do I get the recordset formatted as follows:

foreignkey,primarykey1,primarykey2...primarykey8

---------------------------- OR -----------------

2.009423171,1059645,105946,1059647

------------------------- or even better how too...

2.009423171,anothername1,anothername2,anothername3

... I am using a SQL/Server 2000 database. Please let me know if you need more information.

 

[dnoeth]

This is a common question ;-)

As long as you have an known (and small) number of rows per value it's quite easy:

select
FK,
max(case when cnt = 1 then description else '' end) +
max(case when cnt = 2 then ',' + description else '' end) +
max(case when cnt = 3 then ',' + description else '' end) +
max(case when cnt = 4 then ',' + description else '' end) +
max(case when cnt = 5 then ',' + description else '' end) +
max(case when cnt = 6 then ',' + description else '' end) +
max(case when cnt = 7 then ',' + description else '' end) +
max(case when cnt = 8 then ',' + description else '' end)
from
(
select
FK,
description,
(select count(*) from tab t2
where t1.FK = t2.FK
and t1.PK >= t2.PK) as cnt
from tab t1
) dt
group by FK


btw, you switched PK and FK in your column header....

Dieter

 

[ideafixer]

I am having trouble understanding the last part of the SQL statement.

select
FK,
description,
(select count(*) from tab t2
where t1.FK = t2.FK
and t1.PK >= t2.PK) as cnt
from tab t1
) dt
group by FK

Am I working with 1 table or 2 tables? I can use the table with my foreign key in it if needed. The way I wrote it, I just used the example table in this posting. I have written the statement as follows:

select
ForeignKey,
max(case when cnt = 1 then Description else '' end) +
max(case when cnt = 2 then ',' + Description else '' end) +
max(case when cnt = 3 then ',' + Description else '' end) +
max(case when cnt = 4 then ',' + Description else '' end) +
max(case when cnt = 5 then ',' + Description else '' end) +
max(case when cnt = 6 then ',' + Description else '' end) +
max(case when cnt = 7 then ',' + Description else '' end) +
max(case when cnt = 8 then ',' + Description else '' end)
from
(
select
ForeignKey,Description,
(select count(*) from ExampleTable as t1
where t1.ForeignKey = t2.ForeignKey
and t1.PrimaryKey >= t1.PrimaryKey) as cnt
from ExampleTable as t2
) dt
group by ForeignKey

 

[ideafixer]

OK. I got it to work, after a few tries. I am using the same table as t1 and as t2. Thanks for your help!