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):

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:

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?

CodePudding user response：

Sounds like what you want is just insert the rows back to the table

INSERT INTO tbl ( ID1 , Name , AGE , ID2 , Primary , Secondary )
SELECT ID2 , Name , AGE , ID2 , Secondary , Secondary
FROM   tbl
WHERE  ID2 IS NOT NULL

CodePudding user response：

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

CodePudding user response：

If you want a query to define a vew from you could do the following.

It's not completely clear how you want to generate the ID value but the following will produce your desired results:

select ID, ID1, [Name], AGE, ID2, [Primary], [Secondary]
from t
union all
select  max(id) over()   Row_Number() over (order by id)ID, 
    ID2, [Name], AGE, ID2, [Secondary], [Secondary]
from t
where id2 is not null

CodePudding user response：

To avoid querying the data twice, you can unpivot inside an APPLY

CREATE VIEW YourView
AS
SELECT
  t.ID,
  v.ID1,
  t.Name,
  t.ID2,
  v.Primary,
  t.Secondary
FROM YourTable t
CROSS APPLY (VALUES
    SELECT ID1, Primary
    UNION ALL
    SELECT ID2, Secondary
    WHERE ID2 IS NOT NULL
) v

GO