Sql-server – Generating two rows from one source row

querysql server

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.

Best Answer

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