UNPIVOT을 이용한 행렬변경
오라클 11g 이상에서 가능함
-- 원본 PLANT CUSTOMER PART PLAN_DATE PLANT_QTY IMPORT_DATE IMPORT_QTY PROC_DATE PROC_QTY LOSS_DATE LOSS_QTY ----- --------- ----- --------- ---------- ----------- ---------- --------- ---------- --------- ---------- PLANT CUSTOMER1 PART1 20180101 100 20180102 200 20180103 300 20180103 10 PLANT CUSTOMER1 PART2 20180111 100 20180102 200 20180103 300 20180103 10 PLANT CUSTOMER2 PART3 20180121 100 20180110 200 20180103 300 20180103 10 PLANT CUSTOMER3 PART4 20180131 100 20180112 200 20180103 300 20180103 10 WITH T1( PLANT , CUSTOMER , PART , PLAN_DATE , PLANT_QTY , IMPORT_DATE , IMPORT_QTY , PROC_DATE , PROC_QTY , LOSS_DATE , LOSS_QTY ) AS (SELECT 'PLANT' AS PLANT , 'CUSTOMER1' AS CUSTOMER , 'PART1' AS PART , '20180101' AS PLAN_DATE , 100 AS PLANT_QTY , '20180102' AS IMPORT_DATE , 200 AS IMPORT_QTY , '20180103' AS PROC_DATE , 300 AS PROC_QTY , '20180103' AS LOSS_DATE , 10 AS LOSS_QTY FROM DUAL UNION ALL SELECT 'PLANT' , 'CUSTOMER1' , 'PART2' , '20180111' , 100 , '20180102' , 200 , '20180103' , 300 , '20180103' , 10 FROM DUAL UNION ALL SELECT 'PLANT' , 'CUSTOMER2' , 'PART3' , '20180121' , 100 , '20180110' , 200 , '20180103' , 300 , '20180103' , 10 FROM DUAL UNION ALL SELECT 'PLANT' , 'CUSTOMER3' , 'PART4' , '20180131' , 100 , '20180112' , 200 , '20180103' , 300 , '20180103' , 10 FROM DUAL) SELECT PLANT , CUSTOMER , PART , DATA , DATE1 , QTY FROM T1 UNPIVOT ((QTY, DATE1) FOR DATA IN ((PLANT_QTY, PLAN_DATE) AS 'PLAN' , (IMPORT_QTY, IMPORT_DATE) AS 'IMPORT' , (PROC_QTY, PROC_DATE) AS 'PROC' , (LOSS_QTY, LOSS_DATE) AS 'LOSS')) -- 결과 PLANT CUSTOMER PART DATA DATE1 QTY ----- --------- ----- ------ -------- ---------- PLANT CUSTOMER1 PART1 PLAN 20180101 100 PLANT CUSTOMER1 PART1 IMPORT 20180102 200 PLANT CUSTOMER1 PART1 PROC 20180103 300 PLANT CUSTOMER1 PART1 LOSS 20180103 10 PLANT CUSTOMER1 PART2 PLAN 20180111 100 PLANT CUSTOMER1 PART2 IMPORT 20180102 200 PLANT CUSTOMER1 PART2 PROC 20180103 300 PLANT CUSTOMER1 PART2 LOSS 20180103 10 PLANT CUSTOMER2 PART3 PLAN 20180121 100 PLANT CUSTOMER2 PART3 IMPORT 20180110 200 PLANT CUSTOMER2 PART3 PROC 20180103 300 PLANT CUSTOMER2 PART3 LOSS 20180103 10 PLANT CUSTOMER3 PART4 PLAN 20180131 100 PLANT CUSTOMER3 PART4 IMPORT 20180112 200 PLANT CUSTOMER3 PART4 PROC 20180103 300 PLANT CUSTOMER3 PART4 LOSS 20180103 10 16 rows selected.관련URL : http://www.gurubee.net/article/79704
최신 댓글