I have a UPC table with below records
SKU ATTR_NAME ATTR_VALUE
--------- --------- ----------
38890630 COLOR Black
38890630 DISC Y
38890630 SIZE 8
And I want the output as below
SKU COLOR SIZE
--------- ------ ----
38890630 Black 8
I tried with multiple ways but couldn't able to get the desired output. Can some one help on this?
You can use conditional aggregation:
select sku,
max(case when attr_name = 'COLOR' then attr_value end) as color,
max(case when attr_name = 'DISC' then attr_value end) as disc,
max(case when attr_name = 'SIZE' then attr_value end) as size
from t
group by sku;
You can use PIVOT query :
WITH UPC_TABLE AS (
SELECT 38890630 AS SKU, 'COLOR' AS ATTR_NAME, 'BLACK' AS ATTR_VALUE FROM DUAL
UNION ALL SELECT 38890630 AS SKU, 'DISC' AS ATTR_NAME, 'Y' AS ATTR_VALUE FROM DUAL
UNION ALL SELECT 38890630 AS SKU, 'SIZE' AS ATTR_NAME, '8' AS ATTR_VALUE FROM DUAL
)
SELECT * FROM
UPC_TABLE PIVOT (MAX(ATTR_VALUE) AS ATTR_VALUE FOR (ATTR_NAME) IN (
'COLOR' AS COLOR
, 'DISC' AS DISC
, 'SIZE' AS SIZE_FIELD
)
);
