[ORACLE] 여러 로우 값을 하나의 컬럼으로 바꾸는 방법

* 한 컬럼 중복제거 후 이어 붙이기

 

SELECT LAW_ID,

       SUBSTR(XMLAGG(SYS.XMLTYPE.CREATEXML('<'||FLAG||'>,' || CONTRACT_GESTALT||'</'||FLAG||'>') ORDER BY CONTRACT_GESTALT).EXTRACT('/a1/text()'), 2) CONTRACT_GESTALT,

       SUBSTR(XMLAGG(SYS.XMLTYPE.CREATEXML('<'||FLAG||'>,' || MEMO1||'</'||FLAG||'>') ORDER BY MEMO1).EXTRACT('/a1/text()'), 2) MEMO1

 FROM (

       SELECT

           LAW_ID,

           CONTRACT_GESTALT,NVL2(CONTRACT_GESTALT, 'a' || ROW_NUMBER() OVER (PARTITION BY LAW_ID, CONTRACT_GESTALT ORDER BY CONTRACT_GESTALT),

           'a2')  AS FLAG,

           MEMO1,NVL2(MEMO1, 'a' || ROW_NUMBER() OVER (PARTITION BY LAW_ID, MEMO1 ORDER BY MEMO1),

           'a3')  AS FLAG1

       FROM LAW_OP_FUND)      

GROUP BY LAW_ID

 

* 중복제거 없이 이어 붙이기

 

SELECT 

    LAW_ID,

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

FROM (SELECT LAW_ID, CONTRACT_GESTALT,

       ROW_NUMBER () OVER (PARTITION BY LAW_ID ORDER BY CONTRACT_GESTALT) rnum

       FROM LAW_OP_FUND)

START WITH rnum = 1

CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR LAW_ID = LAW_ID

GROUP BY LAW_ID

 

또는

 

SELECT

    LAW_ID,

    MAX(LTRIM(SYS_CONNECT_BY_PATH(CONTRACT_GESTALT,','),',')) AS CONTRACT_GESTALT

FROM

    (

    SELECT LAW_ID,CONTRACT_GESTALT,ROW_NUMBER() OVER(PARTITION BY LAW_ID ORDER BY ROWNUM) AS RN

    FROM LAW_OP_FUND T1

    --where LAW_ID ='2014091700002'

    )

START WITH RN = 1

CONNECT BY PRIOR LAW_ID = LAW_ID AND PRIOR RN = RN - 1

GROUP BY LAW_ID

ORDER BY LAW_ID ASC

 

출처 - http://amnesis.tistory.com/13