아래 DATA절을 이용해 OUT_DATA절의 데이터로 만들고 싶습니다.
YEAR, LOCAL을 합쳐서 보고 싶습니다.
쿼리 좀 부탁드립니다.
감사합니다.
WITH DATA
AS (
SELECT 2021 YEAR, 'A' LOCAL, 1 CATEGORY1, 2 CATEGORY2 FROM DUAL
UNION ALL
SELECT 2021 YEAR, 'B' LOCAL, 3 CATEGORY1, 4 CATEGORY2 FROM DUAL
UNION ALL
SELECT 2021 YEAR, 'C' LOCAL, 5 CATEGORY1, 6 CATEGORY2 FROM DUAL
UNION ALL
SELECT 2022 YEAR, 'A' LOCAL, 3 CATEGORY1, 4 CATEGORY2 FROM DUAL
UNION ALL
SELECT 2022 YEAR, 'B' LOCAL, 5 CATEGORY1, 6 CATEGORY2 FROM DUAL
UNION ALL
SELECT 2022 YEAR, 'C' LOCAL, 7 CATEGORY1, 8 CATEGORY2 FROM DUAL
)
SELECT * FROM DATA
WITH OUT_DATA
AS (
SELECT 2021 LAST_YEAR, 'A' LOCAL, 1 LAST_CATEGORY1, 2 LAST_CATEGORY2, 2022 YEAR, 3 CATEGORY1, 4 CATEGORY2 FROM DUAL
UNION ALL
SELECT 2021 LAST_YEAR, 'B' LOCAL, 3 LAST_CATEGORY1, 4 LAST_CATEGORY2, 2022 YEAR, 5 CATEGORY1, 6 CATEGORY2 FROM DUAL
UNION ALL
SELECT 2021 LAST_YEAR, 'C' LOCAL, 5 LAST_CATEGORY1, 6 LAST_CATEGORY2, 2022 YEAR, 7 CATEGORY1, 8 CATEGORY2 FROM DUAL
)
SELECT * FROM OUT_DATA
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | WITH data AS ( SELECT 2021 year , 'A' local , 1 category1, 2 category2 FROM dual UNION ALL SELECT 2021, 'B' , 3, 4 FROM dual UNION ALL SELECT 2021, 'C' , 5, 6 FROM dual UNION ALL SELECT 2022, 'A' , 3, 4 FROM dual UNION ALL SELECT 2022, 'B' , 5, 6 FROM dual UNION ALL SELECT 2022, 'C' , 7, 8 FROM dual ) SELECT * FROM data PIVOT ( MIN ( year ) year , MIN (category1) category1 , MIN (category2) category2 FOR year IN (2021 last , 2022 this) ) ORDER BY local ; |
1 2 3 4 5 6 7 8 9 10 11 | SELECT local , MIN (DECODE( year , 2021, year )) last_year , MIN (DECODE( year , 2021, category1)) last_category1 , MIN (DECODE( year , 2021, category2)) last_category2 , MIN (DECODE( year , 2022, year )) this_year , MIN (DECODE( year , 2022, category1)) this_category1 , MIN (DECODE( year , 2022, category2)) this_category2 FROM data GROUP BY local ORDER BY local ; |