SQL - One row with multiple values

[sandylou]

I have a table which stores many codes for different systems. I would like to create a query to show every code for the 2 systems. For example, the data might look like this:

class[tab]code[tab]system
123[tab][tab]v1[tab]A
123[tab][tab]a2[tab]A
123[tab][tab]x1[tab]B
123[tab][tab]g2[tab]B
125[tab][tab]v1[tab]A
125[tab][tab]x1[tab]B
125[tab][tab]g2[tab]B


I would like my data to look like this:

Class[tab]ACode1[tab]Acode2[tab]BCode1[tab]Bcode2
123[tab][tab]v1[tab][tab][tab]a2[tab][tab][tab]x1[tab][tab][tab]g2
125[tab][tab]v1[tab][tab][tab]null[tab][tab][tab]x1[tab][tab][tab]g2

I am not sure how many different codes there would be, so i would need to dynamically create the code values for each system accross.

Would it be best that i create 2 separate cursors to loop through to create the headers and values accross and then join back to the table??

 

[r937]

it would be best to do this in your front-end application

alternatively, look into using PIVOT

http://msdn.microsoft.com/en-us/library/ms177410.aspx

r937.com | rudy.ca

 

[sandylou]

the pivot example can only be used in 2008. i would do it on the front end (asp.net) but not sure how to combine it so it is one set of data. i wanted to use a datagrid, so if they selected the row, some of the values can be edited..

 

[EKOnerhime]

Rob Volk has created a dynamic cross-tabs/pivot table script that may meet your needs:

http://www.sqlteam.com/item.asp?ItemID=2955

This article generated a huge number of responses, it's worth reading through them:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216

 

[sandylou]

Thanks I will read it, i was thinkig i would have to do a cursor and loop through to build a recordset accross, i will check this out and see! I appreciate it!