Search code examples
sqloraclepivot

Oracle Pivot the rows into columns

I have a table A with the following records.

 Use_code | SITE_NUM | rep_name
 BILL_TO       119     abc
 SHIP_TO       119     xyz
 BILL_TO       120     jkf
 SHIP_TO       121     nir

I need to update a table B like below. For each SITE_NUM we need to update BILL_TO and SHIP_TO records rep_name's under different columns like below. 

 SITE_NUM | BILL_TO_REP_NAME | SHIP_TO_REP_NAME
 119      | abc              | xyz
 120      | jkf              | null
 121      | nul              | nir

any help would be greatly appreciated. Thank you.

Solution

  • You can use conditional aggregation to generate the results:

    select a.site_num,
       max(case when a.use_code = 'BILL_TO' then rep_name end) as bill_to,
       max(case when a.use_code = 'BILL_FROM' then rep_name end) as bill_from
from a
group by a.site_num;

    To update the rows using this information, you can use merge. It is not obvious if you really need an update, though.