I have a table containing transactions with two different types of transactions (transfers & orders), the buyer_id is only stored with a transfer event however the price is stored on the order event. How do I merge these two rows so that I have a single row with the buyer_id and price on the same line?

Here's what the table looks like:

Order ID   Trans ID    Timestamp            Category ID   Buyer ID   Price   Event type
4646820767  347571948  2022-04-02 10:05:48   8699308                 75      order
4646820796  347571948  2022-04-02 10:05:48   8699308      2530070            transfer

Here's what I've tried so far:

select a.order_id, a.asset_id, a.timestamp, a.category_id, b.buyer_id, a.price
from table a 
inner join table b
  on a.trans_id = b.trans_id

CodePudding user response：

Your query is not wrong; but mot complete; It needs two additional condition.

select a.order_id, a.asset_id, a.timestamp, a.category_id, b.buyer_id, a.price
from table a 
inner join table b
  on a.trans_id = b.trans_id and a.Buyer_id is not null and a.price is not null

CodePudding user response：

We can use lead() and lag() to fill the missing info in every line instead of using an inner join of the entire table.

select  Order_ID
       ,Trans_ID
       ,time 
       ,Category_ID 
       ,case when Buyer_ID is null then lead(Buyer_ID) over(partition by trans_id order by order_id) else Buyer_ID end as Buyer_ID
       ,case when price is null then lag(price) over(partition by trans_id order by order_id) else price end           as price
      ,Event_type

from    t

Fiddle