3

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.

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.