[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