asked on
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
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
SQL
Last Comment
Andrei Fomitchev
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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] <> ''
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] <> ''
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.