데이터베이스 질문과 답변
지금 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
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
;
감사합니다!!
) 버튼을 클릭하여 작성 하시면 됩니다.