I have a dataset that looks like this (where the codes of ID1 and ID2 can never be the same. So putting them together they are still unique): ID ID1 Name AGE ID2 Primary Secondary 1 1234 Jim 34 1111 Mars A 2 1234 Tom 24 1111 Mars A 3 1234 Rick 55 1112 Mars B 4 2222 Ann 22

Unfold Data by Column

I have a dataset that looks like this (where the codes of ID1 and ID2 can never be the same. So putting them together they are still unique):

ID	ID1	Name	AGE	ID2	Primary	Secondary
1	1234	Jim	34	1111	Mars	A
2	1234	Tom	24	1111	Mars	A
3	1234	Rick	55	1112	Mars	B
4	2222	Ann	22		Jupiter
5	2222	Liz	54	3000	Jupiter	A
6	2222	Frey	19	3000	Jupiter	A

What I want to do is for each person that has a value in ID2 I want to add this person at the bottom and want to exchange ID1 with ID2 and Primary with Secondary entries. So in the view that I want to create the entries above would be followed with this entries here:

ID	ID1	Name	AGE	ID2	Primary	Secondary
7	1111	Jim	34	1111	A	A
8	1111	Tom	24	1111	A	A
9	1112	Rick	55	1112	B	B
10	3000	Liz	54	3000	A	A
11	3000	Frey	19	3000	A	A

I tried a lot of things. Like creating separate temp tables for the ID2 values but I just cannot come up with an idea to finish this. Anyone who can help me with this?

Answer

Select ID,ID1,Name,ID2,Primary,Secondary From Tab1
UNION ALL
Select ID,ID2 as ID1 ,Name,ID2,Secondary AS Primary,Secondary From Tab1
WHERE ID2 IS NOT NULL

Advertisement

Answer