1

I am having a table containing the following data

SKU     Site  Code    Value
0890    50    BRAND   OMH
0890    50    CSBC    0763440890
0890    50    DWPS    0763440890
0890    50    DWS     0763440890
0890    50    DWSG    *
0890    50    EA      1

I am looking to convert it into a table like the following

SKU     BRAND   CSBC        DWPS        DWS         DWSG    EA
0890    OMH     0763440890  0763440890  0763440890  *       1

The version of the Oracle db we have is 10G so PIVOT is not useful yet.

Please help.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
PURWU
  • 397
  • 1
  • 8
  • 22
  • Is the entire set of possible Code identifiers known in advance? If not, you would need dynamic SQL (even WITH the `pivot` operator, even in Oracle 12.2, the current version). –  Jan 17 '18 at 16:35

1 Answers1

2

Oracle 10 doesn't have pivot, so you can use conditional aggregation:

select sku,
       max(case when code = 'BRAND' then value end) as brand,
       max(case when code = 'CSBC' then value end) as CSBC,
       max(case when code = 'DWPS' then value end) as DWPS,
       max(case when code = 'DWS' then value end) as DWS,
       max(case when code = 'DWSG' then value end) as DWSG,
       max(case when code = 'EA' then value end) as ea
from t
group by sku;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786