다중 컬럼 PIVOT 쿼리 문의 드립니다. 0 2 798

by 날자~! [SQL Query] [2022.01.19 13:39:15]


지금 DATA 절의 데이터 형태를

OUTPUT_DATA 데이터 형태로 바꾸고 싶은데 

쿼리 좀 부탁드립니다.

 

DATA_A, DATA_B 컬럼을 YEAR 기준으로 컬럼별로 열에서 행으로 변환 하고자 합니다.

 

WITH DATA
AS (
SELECT '2001' YEAR, '200101' MONTH, '1' DATA_A, '2' DATA_B FROM DUAL
UNION ALL
SELECT '2001' YEAR, '200102' MONTH, '3' DATA_A, '4' DATA_B FROM DUAL
UNION ALL
SELECT '2001' YEAR, '200103' MONTH, '5' DATA_A, '6' DATA_B FROM DUAL 
UNION ALL
SELECT '2002' YEAR, '200201' MONTH, '7' DATA_A, '8' DATA_B FROM DUAL
UNION ALL
SELECT '2002' YEAR, '200202' MONTH, '9' DATA_A, '10' DATA_B FROM DUAL
UNION ALL
SELECT '2002' YEAR, '200203' MONTH, '11' DATA_A, '12' DATA_B FROM DUAL
)
SELECT * FROM DATA

 

WITH OUTPUT_DATA
AS (
SELECT '2001' YEAR, 'A' CATEGORY, '1' MONTH1, '3' MONTH2, '5' MONTH3 FROM DUAL
UNION ALL
SELECT '2001' YEAR, 'B' CATEGORY, '2' MONTH1, '4' MONTH2, '6' MONTH3 FROM DUAL
UNION ALL
SELECT '2002' YEAR, 'A' CATEGORY, '7' MONTH1, '9' MONTH2, '11' MONTH3 FROM DUAL
UNION ALL
SELECT '2002' YEAR, 'B' CATEGORY, '8' MONTH1, '10' MONTH2, '12' MONTH3 FROM DUAL
)
SELECT * FROM OUTPUT_DATA

by 마농 [2022.01.19 14:08:04]
WITH data AS
(
SELECT '2001' year, '200101' month, 1 data_a, 2 data_b FROM dual
UNION ALL SELECT '2001', '200102', 3, 4 FROM dual
UNION ALL SELECT '2002', '200201', 5, 6 FROM dual
UNION ALL SELECT '2002', '200202', 7, 8 FROM dual
)
SELECT *
  FROM (SELECT year
             , SUBSTR(month, 5) mm
             , data_a
             , data_b
          FROM data
        )
 UNPIVOT (v FOR category IN (data_a AS 'A', data_b AS 'B'))
 PIVOT (MIN(v) FOR mm IN ('01' m01, '02' m02, '03' m03))
 ORDER BY year, category
;

 


by 날자~! [2022.01.19 14:17:36]

감사합니다!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입