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

DataBase/Sql

Sort 2014. 10. 23. 13:27

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

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