Generating two rows from one source row

Question

I have a table which has columns:

• txn_id
• from_user_id
• to_user_id
• price

I want a query that produces two rows for each source row. The output would look like:

• focal_user_id (new)
• txn_id
• from_user_id
• to_user_id
• price

The focal_user_id value is generated from from_user_id for the first row output, and generated from to_user_id for the second row.

I need to do this in SQL rather than code because then I can make use of WHERE and LIMIT, as well as do some joins based on the new focal_user_id column.

Answer 1

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