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
try:
or
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
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
