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?
Advertisement
Answer
x
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