Avatar of tmajor99
tmajor99
 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
SQL

Avatar of undefined
Last Comment
Andrei Fomitchev

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ryan Chong

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

What database product and version?


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] <> ''
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23