[Sql] 여러건의 특정 컬럼 행으로 표시하기.

여러건의 특정 컬럼 행으로 표시 하기

SELECT 

        col1

       ,col2

       ,SUBSTR( MAX( SYS_CONNECT_BY_PATH( merge_col, ',' )), 2 ) merge_col

FROM (

                --

                SELECT col1, col2, merge_col, ROW_NUMBER( ) OVER( PARTITION BY col1, col2 ORDER BY col1, col2 ) rn

                FROM ( SELECT 'AAA' AS col1, 'BBB' AS col2, 'S' AS merge_col 

                FROM DUAL

                UNION ALL

                SELECT 'AAA' AS col1, 'BBB' AS col2, 'T' AS merge_col

                FROM DUAL

                UNION ALL

                SELECT 'AAA' AS col1, 'BBB' AS col2, 'E' AS merge_col

                FROM DUAL

                UNION ALL

                SELECT 'AAA' AS col1, 'BBB' AS col2, 'E' AS merge_col

                FROM DUAL

                UNION ALL

                SELECT 'AAA' AS col1, 'BBB' AS col2, 'L' AS merge_col

                FROM DUAL

                UNION ALL

                SELECT 'CCC' AS col1, 'DDD' AS col2, 'C' AS merge_col

                FROM DUAL

                UNION ALL

                SELECT 'CCC' AS col1, 'DDD' AS col2, 'A' AS merge_col

                FROM DUAL

                UNION ALL

                SELECT 'CCC' AS col1, 'DDD' AS col2, 'T' AS merge_col

                FROM DUAL )

                --

)

START WITH rn = 1

CONNECT BY PRIOR col1 = col1

AND col2 = col2

AND PRIOR rn = rn - 1

GROUP BY col1, col2

출처 : http://okhun.tistory.com/57