[ORACLE/SQL] 여러 행을 컬럼 합치기 (WM_CONCAT, LISTAGG, XMLAGG, XMLELEMENT, 중복제거)

오라클의 여러행을 하나의 컬럼으로 합치는 쿼리를 메모하고져 글을 남깁니다.

자세한 설명은 하지 않겠습니다.

(XMLAGG 및 정규식은 10g부터 사용 가능)

• WM_CONCAT

SELECT
    WM_CONCAT(job) AS WM_JOBS
  FROM emp

 

• LISTAGG

SELECT
    LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
FROM emp

 

• LISTAGG (deptno 기준)

SELECT
    LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) OVER(PARTITION BY deptno) AS AGG_JOBS2
FROM emp

 

• LISTAGG (중복제거)

SELECT
    LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
    , REGEXP_REPLACE( LISTAGG(job, ',') WITHIN GROUP(ORDER BY job), '([^,]+)(,\1)*(,|$)', '\1\3') AS AGG_JOBS3
FROM emp

 

• XMLAGG, XMLELEMENT (CLOB타입 - WM_CONCAT, LISTAGG : VARCHAR2라 최대 4000byte)

SELECT
    XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS
FROM emp A

 

• XMLAGG, XMLELEMENT (CLOB타입, 중복제거, 시작/끝 구분자 삭제)

WITH A AS
(
SELECT
    XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS1
    , XMLAGG(XMLELEMENT(A, ',' || A.job ) ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS2
FROM emp A
)
SELECT 
    SUBSTR(A.CLOB_JOBS1, 1, LENGTH(A.CLOB_JOBS1) - 1) AS SUB_JOBS1
    , SUBSTR(A.CLOB_JOBS2, 2) AS SUB_JOBS2
    , SUBSTR(REGEXP_REPLACE ( A.CLOB_JOBS2, '([^,]+)(,\1)*(,|$)', '\1\3'), 2) AS SUB_JOB3
FROM A

 

• 통합 (LISTAGG -PARTITION 제외)

WITH A AS
(
SELECT
    WM_CONCAT(job) AS WM_JOBS
    , LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
    , REGEXP_REPLACE( LISTAGG(job, ',') WITHIN GROUP(ORDER BY job), '([^,]+)(,\1)*(,|$)', '\1\3') AS AGG_JOBS3
    , XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS1
    , XMLAGG(XMLELEMENT(A, ',' || A.job ) ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS2
FROM emp A
)
SELECT
    A.*
    , SUBSTR(A.CLOB_JOBS1, 1, LENGTH(A.CLOB_JOBS1) - 1) AS SUB_JOBS1
    , SUBSTR(A.CLOB_JOBS2, 2) AS SUB_JOBS2
    , REGEXP_REPLACE( REGEXP_REPLACE ( A.CLOB_JOBS1, '([^,]+)(,\1)*(,|$)', '\1\3'), '(,)$', '') AS SUB_JOB3
    , REGEXP_REPLACE( REGEXP_REPLACE ( A.CLOB_JOBS2, '([^,]+)(,\1)*(,|$)', '\1\3'), '^(,)', '') AS SUB_JOB4
FROM A

 

※ ORA-01489 문자열 연결의 결과가 너무 깁니다.

 : 오류 발생시 XMLAGG를 이용하여 CLOB 사용.