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

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다