3 Suggested additional solution using APPLY edited Nov 1 '18 at 5:06 Paul White says GoFundMonica♦ 50k1616 gold badges314314 silver badges495495 bronze badges Unless I completely destroy the question in my head this is what you meant by two rows out of each one: ``````create table #TableOne ( TxnId int, FromUserId int, ToUserId int, Price decimal(5,2)) ; insert into #TableOne values (1, 1, 2, 100.01), (2, 1, 2, 100.01), (3, 2, 1, 100.01), (4, 2, 1, 100.01), (5, 3, 4, 100.01), (6, 4, 3, 100.01), (7, 4, 3, 100.01) ; select FromUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne union all select ToUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne ; `````` Alternatively, using `APPLY` (lateral join): ``````SELECT CA.FocalUserId, TON.TxnId, TON.FromUserId, TON.ToUserId, TON.Price FROM #TableOne AS TON CROSS APPLY ( VALUES (TON.FromUserId), (TON.ToUserId) ) AS CA (FocalUserId); `````` Unless I completely destroy the question in my head this is what you meant by two rows out of each one: ``````create table #TableOne ( TxnId int, FromUserId int, ToUserId int, Price decimal(5,2)) ; insert into #TableOne values (1, 1, 2, 100.01), (2, 1, 2, 100.01), (3, 2, 1, 100.01), (4, 2, 1, 100.01), (5, 3, 4, 100.01), (6, 4, 3, 100.01), (7, 4, 3, 100.01) ; select FromUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne union all select ToUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne ; `````` Unless I completely destroy the question in my head this is what you meant by two rows out of each one: ``````create table #TableOne ( TxnId int, FromUserId int, ToUserId int, Price decimal(5,2)) ; insert into #TableOne values (1, 1, 2, 100.01), (2, 1, 2, 100.01), (3, 2, 1, 100.01), (4, 2, 1, 100.01), (5, 3, 4, 100.01), (6, 4, 3, 100.01), (7, 4, 3, 100.01) ; select FromUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne union all select ToUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne ; `````` Alternatively, using `APPLY` (lateral join): ``````SELECT CA.FocalUserId, TON.TxnId, TON.FromUserId, TON.ToUserId, TON.Price FROM #TableOne AS TON CROSS APPLY ( VALUES (TON.FromUserId), (TON.ToUserId) ) AS CA (FocalUserId); `````` 2 add terminators edited Oct 31 '18 at 21:08 ypercubeᵀᴹ 83.2k1111 gold badges141141 silver badges234234 bronze badges Unless I completely destroy the question in my head this is what you meant by two rows out of each one: ``````create table #TableOne ( TxnId int, FromUserId int, ToUserId int, Price decimal(5,2)) ; insert into #TableOne values (1, 1, 2, 100.01), (2, 1, 2, 100.01), (3, 2, 1, 100.01), (4, 2, 1, 100.01), (5, 3, 4, 100.01), (6, 4, 3, 100.01), (7, 4, 3, 100.01) ; select FromUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne union all select ToUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne ; `````` Unless I completely destroy the question in my head this is what you meant by two rows out of each one: ``````create table #TableOne ( TxnId int, FromUserId int, ToUserId int, Price decimal(5,2)) insert into #TableOne values (1, 1, 2, 100.01), (2, 1, 2, 100.01), (3, 2, 1, 100.01), (4, 2, 1, 100.01), (5, 3, 4, 100.01), (6, 4, 3, 100.01), (7, 4, 3, 100.01) select FromUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne union all select ToUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne `````` Unless I completely destroy the question in my head this is what you meant by two rows out of each one: ``````create table #TableOne ( TxnId int, FromUserId int, ToUserId int, Price decimal(5,2)) ; insert into #TableOne values (1, 1, 2, 100.01), (2, 1, 2, 100.01), (3, 2, 1, 100.01), (4, 2, 1, 100.01), (5, 3, 4, 100.01), (6, 4, 3, 100.01), (7, 4, 3, 100.01) ; select FromUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne union all select ToUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne ; `````` 1 answered Jun 6 '17 at 16:14 Nelz 1,60033 gold badges1010 silver badges2525 bronze badges Unless I completely destroy the question in my head this is what you meant by two rows out of each one: ``````create table #TableOne ( TxnId int, FromUserId int, ToUserId int, Price decimal(5,2)) insert into #TableOne values (1, 1, 2, 100.01), (2, 1, 2, 100.01), (3, 2, 1, 100.01), (4, 2, 1, 100.01), (5, 3, 4, 100.01), (6, 4, 3, 100.01), (7, 4, 3, 100.01) select FromUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne union all select ToUserId AS FocalUserId, TxnId, FromUserId, ToUserId, Price from #TableOne ``````