GUBUN | DIV | NUM | PRICE
--------------------------------
SNACK | 100 | 30 | 3000
SNACK | 400 | 24 | 9000
이렇게 되어있는 데이터를
GUBUN | DIV1 | DIV2
------------------------
SNACK | 100 | 400
SNACK | 3000 | 9000
이렇게 행열을 바꾸고 싶습니다.
PIVOT/UNPIVOT을 써보고있는데
WITH SELL_DATA AS
( SELECT 'SNACK' GUBUN, 100 DIV, 30 NUM, 3000 PRICE
FROM DUAL
UNION ALL
SELECT 'SNACK' GUBUN, 400 DIV, 24 NUM, 9600 PRICE
FROM DUAL )
SELECT *
FROM SELL_DATA
UNPIVOT ( PRICE FOR DIV2 IN ( DIV, PRICE ) )
이렇게 했더니
100
3000
400
9000
이렇게 값이 아래로만 내려가내요ㅠㅠ
행열 변환 방법 알려주세요ㅠㅠ
WITH sell_data AS ( SELECT 'SNACK' gubun, 100 div, 30 num, 3000 price FROM dual UNION ALL SELECT 'SNACK', 400, 24, 9000 FROM dual ) SELECT * FROM (SELECT gubun, div, num, price , ROW_NUMBER() OVER(PARTITION BY gubun ORDER BY num) rn FROM sell_data ) UNPIVOT (v FOR gb IN (div, num, price)) PIVOT(MIN(v) FOR rn IN (1, 2, 3, 4, 5, 6)) ;