SQL Select to change record orientation from horizontal to vertical
I need a SQL select that can change the orientation of my record. My input file contains one row per unique ID with separate columns for each reference ID and Type. I need to format each row by breaking out each column reference type and ID into a new row.
Input Table:
ID Reference1 ID Reference1 Type Reference 2 ID Reference2 Type
-------- ------------- ------------- ----------------- -----------------
10000 82 GPH 63 LineBuy
10001 55 GPH 43 LineBuy
10002 32 LineBuy
Output Table:
ID Reference ID Reference Type
--------- ------------- ----------------
10000 82 GPH
10000 63 LineBuy
10001 55 GPH
10001 43 LineBuy
10002 32 LineBuy
Look at unpivot. Most SQL languages have that now.
Scott Pletcher
SELECT it.ID, ca1.* FROM dbo.input_table it CROSS APPLY ( VALUES([Reference1 ID],[Reference1 Type]),([Reference2 ID],[Reference2 Type]) /*, ...*/ ) AS ca1([Reference ID], [Reference Type]) WHERE [Reference ID] <> ''
Andrei Fomitchev
SELECT * FROM (
SELECT [ID], [Reference1 ID] AS [Reference ID], [Reference1 Type] FROM [Input Table]
UNION ALL
SELECT [ID], [Reference2 ID] AS [Reference ID], [Reference2 Type] FROM [Input Table]
) t
WHERE t.[Reference ID] <> ''
What database product and version?
Look at unpivot. Most SQL languages have that now.