We help IT Professionals succeed at work.

SQL Select to change record orientation from horizontal to vertical

tmajor99
tmajor99 asked
on
Medium Priority
41 Views
Last Modified: 2020-02-28
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
Comment
Watch Question

Software Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT
Commented:
try:

select
ID , [Reference1 ID] as Reference ID ,  [Reference1 Type] as  Reference Type
from yourTable
where  [Reference1 ID] is not null
union
select
ID , [Reference2 ID] as Reference ID ,  [Reference2 Type] as  Reference Type
from yourTable
where  [Reference2 ID] is not null

Open in new window


or
select
ID , [Reference1 ID] as Reference ID ,  [Reference1 Type] as  Reference Type
from yourTable
where  [Reference1 ID] is not null
union all
select
ID , [Reference2 ID] as Reference ID ,  [Reference2 Type] as  Reference Type
from yourTable
where  [Reference2 ID] is not null

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

What database product and version?


Look at unpivot.  Most SQL languages have that now.

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:


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] <> ''


CERTIFIED EXPERT

Commented:
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] <> ''

Explore More ContentExplore courses, solutions, and other research materials related to this topic.