How do I transform multiple columns of data to unique rows?

[superset]

Hi,

I've inherited a poorly designed database and I'm hoping you can help me solve a problem.

I have data that is organized similar the following (the actual data set is much larger).

Name ID1 ID2 ID3 ID4
--------------------------------------------
Dave D1 D2 D3 D4
Tammy T1 T2 T3 T4
Merrick M1 M2 M2 M4

I need to create a SQL query (or something) that will organize the data like this:

Name ID
------------------
Dave D1
Dave D2
Dave D3
Dave D4
Tammy T1
Tammy T2
Tammy T3
Tammy T4
Merrick M1
Merrick M2
Merrick M3
Merrick M4

Does anyone have any thoughts?

-Dave

[ck9663]

Yes. Use PIVOT/UNPIVOT to accomplish your goal.

Happy Coding!!!

~~ CK

[nbiswas]

Options

a) Use UNPIVOT

b) Use Union All

e.g.

Select Name, Id1 as Id from tblDummy UNION ALL
Select Name, Id2 from tblDummy UNION ALL
Select Name, Id3 from tblDummy UNION ALL
Select Name, Id4 from tblDummy


Hope this helps