다중row를 컬럼으로 변경 (ORACLE)

-------------------------------

-- 1번째 방법 : XMLAGG

-------------------------------

- 예제

    WITH T1 AS (

        SELECT 'A' AS COL1 FROM DUAL UNION ALL

        SELECT 'B' FROM DUAL UNION ALL

        SELECT 'C' FROM DUAL UNION ALL

        SELECT 'D' FROM DUAL UNION ALL

        SELECT 'D' FROM DUAL

    )

    SELECT

        SUBSTR(XMLAGG(XMLELEMENT(A, ',' || COL1) ORDER BY COL1).EXTRACT('//text()'), 2)

    FROM T1;

------------------------------------------------

-- 2번째 방법 : CONNECT BY AND SYS_CONNECT_BY_PATH

------------------------------------------------

- 예제

    SELECT [KEY],

           SUBSTR(MAX(SYS_CONNECT_BY_PATH([대상컬럼],'@')),2),

    FROM (

             SELECT [KEY], 

                    ROW_NUMBER() OVER(PARTITION BY [KEY] ORDER BY [KEY]) AS RNUM

             FROM [TABLE명]

         ) 

    START WITH RNUM =1 

    CONNECT BY PRIOR RNUM = RNUM -1 AND PRIOR [KEY] = [KEY]

    GROUP BY [KEY]