Oracle/SQL Query
COLS TO ROW - ORACLE LISTAGG(COLUMN, 구분자 )
천정현
2010. 11. 22. 18:43
컬럼 값을 ROW 로 바로 바꿀 수 있는 함수가 나왔다. ㅋㅋ
굿 잡!!
SELECT 'CASE '||LISTAGG(AA,' ')WITHIN GROUP(ORDER BY AA)||' END' RESULT FROM (
SELECT 'WHEN NUM IN ('|| LISTAGG(NUM,',')WITHIN GROUP(ORDER BY DD )||') THEN '||DD AA FROM
(
SELECT 1 NUM , 'B' DD FROM DUAL
UNION ALL
SELECT 2 NUM, 'B' FROM DUAL
UNION ALL
SELECT 3 NUM, 'A' FROM DUAL
UNION ALL
SELECT 4 NUM,'A' FROM DUAL
UNION ALL
SELECT 5 NUM, 'C' FROM DUAL
UNION ALL
SELECT 6 NUM, 'C' FROM DUAL
UNION ALL
SELECT 7 NUM, 'C' FROM DUAL
)
GROUP BY DD
)
;
CASE WHEN NUM IN (1,2) THEN B
WHEN NUM IN (3,4) THEN A
WHEN NUM IN (5,6,7) THEN C
END