[오라클] 열을 한 컬럼에

WITH TB AS (

     SELECT 'A' AS COL_STD, 'A1' COL_DATA FROM DUAL UNION ALL

     SELECT 'A' AS COL_STD, 'A2' COL_DATA FROM DUAL UNION ALL

     SELECT 'A' AS COL_STD, 'A3' COL_DATA FROM DUAL UNION ALL

     SELECT 'B' AS COL_STD, 'B1' COL_DATA FROM DUAL UNION ALL

     SELECT 'B' AS COL_STD, 'B2' COL_DATA FROM DUAL UNION ALL

     SELECT 'B' AS COL_STD, 'B3' COL_DATA FROM DUAL UNION ALL

     SELECT 'C' AS COL_STD, 'C1' COL_DATA FROM DUAL UNION ALL

     SELECT 'C' AS COL_STD, 'C2' COL_DATA FROM DUAL UNION ALL

     SELECT 'C' AS COL_STD, 'C3' COL_DATA FROM DUAL

)

SELECT  TB.COL_STD

            ,REPLACE(LTRIM(SYS_CONNECT_BY_PATH(TB.COL_DATA, '||'), '||'), '||', ',') AS COL_DATA

FROM    (

                   SELECT   TB.COL_STD

                                 ,TB.COL_DATA AS COL_DATA

                                 ,ROW_NUMBER() OVER (PARTITION BY TB.COL_STD ORDER BY TB.COL_STD) RN

                                 ,COUNT(1)  OVER (PARTITION BY TB.COL_STD) CNT

                   FROM      TB

                   ORDER BY TB.COL_STD, TB.COL_DATA

             ) TB

WHERE   LEVEL = CNT

START WITH RN = 1

CONNECT BY PRIOR TB.COL_STD = TB.COL_STD

AND PRIOR RN = RN - 1

;

 

 

[중요 함수]

 - SYS_CONNECT_BY_PATH(&연결할 컬럼명, '구분자')                                 -- 같은 기준 컬럼의 것을 구분자로 묶음

 - ROW_NUMBER() OVER (PARTITION BY &기준컬럼 ORDER BY &기준컬럼)    -- 같은 기준 컬럼끼리 순서를 부여함

 - COUNT(1) OVER (PARTITION BY &기준컬럼)                                             -- 같은 기준 컬럼의 전체 개수 확인

 

 - WHERE  LEVEL = CNT      -- 카운트별로 레벨을 지정

    START WITH RN = 1          -- 1부터 시작하는 것을 찾음

    CONNECT BY PRIOR TB.COL_STD = TB.COL_STD -- 기준컬럼으로 묶음

    AND PRIOR RN = RN - 1