combine many records into one and one record into many 2

[yourfriend]

I am new to SQL server and I would like to like is there any way I can combine many records into one and one record into many.

Input/Output (many records):

id DX
1 A
1 B
1 C
2 A
2 B

Output/Input (one record):

id DX1 DX2 DX3
1 A B C
2 A B

I am a SAS programmer. I can do it pretty easily in SAS but I don't know how to do it in Query Analyser/DTS scripts or whatever. Any help is welcome.

 

[rac2]

If you have a table already and therefore know how many DX columns then the one-into-many is fairly easy.

SELECT id, DX1 AS "DX" FROM tableB
   WHERE DX1 IS NOT NULL

UNION ALL

SELECT id, DX2 AS "DX" FROM tableB
   WHERE DX2 IS NOT NULL

UNION ALL

SELECT id, DX3 AS "DX" FROM tableB
   WHERE DX3 IS NOT NULL

If you know the maximum number of occurrences of rows with the same id and different DX values,
and there is a column that tells which DX is in that row
then it is possible to make the many-into-one.

SELECT DISTINCT a.id, b.DX AS "DX1", c.DX AS "DX2", d.DX AS "DX3"
FROM tableA a
LEFT JOIN (SELECT id, DX FROM tableA WHERE DX_Occurrence = 1) b ON a.id = b.id
LEFT JOIN (SELECT id, DX FROM tableA WHERE DX_Occurrence = 2) c ON a.id = c.id
LEFT JOIN (SELECT id, DX FROM tableA WHERE DX_Occurrence = 3) d ON a.id = d.id

In your example data the value of the DX column appears to identify the occurrence, in my query I made a separate column for occurrence. Or the DX might be identified by type and certain types would go into certain columns.

If you don't know how many occurrences of DX there might be for a given id, then you have a problem in SQL. You would need to build a different table every time you made the transformation of many-into-one. That could be done in a procedure, but not (so far as I know) in a query.

This is a really interesting problem. There have been many variations on it posted in this forum.

 

[tlbroadbent]

Here is one way to create a CrossTab in T-SQL.

Select
ID,
max(Case RowNo When 1 Then DX Else '' End) As DX1,
max(Case RowNo When 2 Then DX Else '' End) As DX2,
max(Case RowNo When 3 Then DX Else '' End) As DX3
From
(Select ID, DX,
RowNo=(Select count(*) From Table1
Where ID=a.ID And DX<=a.DX)
From Table1 As a) as b
Group By ID
Order By ID Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[yourfriend]

hey guys, thanks for the input. I'll try both methods and see which one is better.