오라클 10g - 컬럼명의 언더바 기준으로 단어 분리하기

WITH T AS
(
SELECT 1 NO FROM DUAL UNION ALL
SELECT 2 NO FROM DUAL UNION ALL
SELECT 3 NO FROM DUAL UNION ALL
SELECT 4 NO FROM DUAL UNION ALL
SELECT 5 NO FROM DUAL UNION ALL
SELECT 6 NO FROM DUAL UNION ALL
SELECT 7 NO FROM DUAL UNION ALL
SELECT 8 NO FROM DUAL UNION ALL
SELECT 9 NO FROM DUAL UNION ALL
SELECT 10 NO FROM DUAL
)
SELECT CASE WHEN NO=1 AND POSI=0 THEN COLUMN_NAME
           WHEN NO>1 AND TOP=0  THEN NULL
           WHEN NO=1 AND POSI>0 THEN SUBSTR(COLUMN_NAME,1,POSI-1)
           WHEN NO>1 AND POSI=0 THEN SUBSTR(COLUMN_NAME,TOP+1)
           WHEN NO>1 AND POSI>0 THEN SUBSTR(COLUMN_NAME,TOP+1,POSI-TOP-1)
       END COL_NM
       , SUM(CNT) CNT
FROM
    (
    SELECT A.*, LAG(POSI,1,0) OVER (ORDER BY COLUMN_NAME, NO) TOP
    FROM
        (
        SELECT A.*, T.*, INSTR(A.COLUMN_NAME,'_',1,NO) POSI
        FROM
            (
            SELECT COLUMN_NAME, COUNT(1) CNT
            FROM COLS A
            GROUP BY COLUMN_NAME
            ) A, T
        ORDER BY COLUMN_NAME, NO
        ) A
    ) A
GROUP BY CASE WHEN NO=1 AND POSI=0 THEN COLUMN_NAME
           WHEN NO>1 AND TOP=0  THEN NULL
           WHEN NO=1 AND POSI>0 THEN SUBSTR(COLUMN_NAME,1,POSI-1)
           WHEN NO>1 AND POSI=0 THEN SUBSTR(COLUMN_NAME,TOP+1)
           WHEN NO>1 AND POSI>0 THEN SUBSTR(COLUMN_NAME,TOP+1,POSI-TOP-1)
       END
;