[Oracle] 오라클 LISTAGG 사용 방법 및 예제 (정렬, 중복제거, 컬럼 합치기/이어 붙이기)

오라클에서 쿼리를 작성하다 보면 여러 행의 컬럼 값을 하나로 묶어서 표현해야 하는 경우 LISTAGG 함수를 사용합니다.

 - 합치기, 이어 붙이기

 

LISTAGG 함수는 여러 행의 값을 묶어서 표현하는 기능이므로 GROUP BY 또는 PARTITION BY 를 사용하여 그룹을 지정해야만 사용 가능합니다.

 

LISTAGG의 기본 사용 방법은 다음과 같습니다.

LISTAGG( [합치는 컬럼], [구분자] ) WITHIN GROUP(ORDER BY [정렬 기준 컬럼] )

 

LISTAGG 사용 예제1

SELECT LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME) AS ENAME_LIST
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK')

LISTAGG 사용 예제1

 

위 예시처럼 LISTAGG 단일 컬럼만 선언하거나 그룹 함수만 선언하는 경우 그룹 함수를 지정하지 않고 사용할 수 있습니다. 

LISTAGG 사용 예제2

SELECT JOB
     , LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME) AS ENAME_LIST
     , LISTAGG(DEPTNO, ',') WITHIN GROUP(ORDER BY DEPTNO) AS DEPTNO_LIST
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK')
    GROUP BY JOB

LISTAGG 사용 예제2

 

위 예시처럼 LISTAGG 단일 컬럼만 있는게 아니라면 그룹을 지정해야 사용할 수 있습니다.

LISTAGG 사용 예제3

SELECT JOB
     , LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME) OVER(PARTITION BY JOB) AS ENAME_LIST
     , LISTAGG(DEPTNO, ',') WITHIN GROUP(ORDER BY DEPTNO) OVER(PARTITION BY JOB) AS DEPTNO_LIST
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK')

LISTAGG 사용 예제3

 

그룹을 지정하기 애매한 경우 위 예시처럼 OVER ( PARTITION BY [그룹 지정할 컬럼] ) 를 사용하여 컬럼 자체적으로 그룹을 지정하여 사용할 수 있습니다.

 

하지만 그룹이 지정되지 않아 원래 행의 개수만큼 결과 도출되니 필요에 따라 DISTINCT 로 중복 제거해서 사용해야 합니다.

SELECT DISTINCT JOB
     , LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME) OVER(PARTITION BY JOB) AS ENAME_LIST
     , LISTAGG(DEPTNO, ',') WITHIN GROUP(ORDER BY DEPTNO) OVER(PARTITION BY JOB) AS DEPTNO_LIST
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK')

LISTAGG 사용 예제3 (중복행 제거)

 

LISTAGG 중복 제거 후 합치기

SELECT JOB
     , REGEXP_REPLACE(LISTAGG(DEPTNO, ',') WITHIN GROUP(ORDER BY DEPTNO), '([^,]+)(,\1)*(,|$)', '\1\3') AS DEPTNO_LIST
    FROM EMP
    WHERE JOB IN ('MANAGER','SALESMAN','CLERK','ANALYST')
    GROUP BY JOB

LISTAGG 중복 제거 후 합치기

 

LISTAGG로 합쳐진 값에 중복된 값들이 섞여 있는 경우 LISTAGG를 해도 가독성이 떨어집니다.

이 경우 위 예시처럼 정규식을 사용하여 중복된 값을 제거 후 단일 값만 정렬하여 순차적으로 합칠 수 있습니다.

 

SELECT JOB
     , REGEXP_REPLACE(LISTAGG(DEPTNO, ',') WITHIN GROUP(ORDER BY DEPTNO), '([^,]+)(,\1)*(,|$)', '\1\3') AS DEPTNO_LIST1
     , REGEXP_REPLACE(LISTAGG(DEPTNO, '.') WITHIN GROUP(ORDER BY DEPTNO), '([^.]+)(.\1)*(.|$)', '\1\3') AS DEPTNO_LIST2
     , REGEXP_REPLACE(LISTAGG(DEPTNO, ';') WITHIN GROUP(ORDER BY DEPTNO), '([^;]+)(;\1)*(;|$)', '\1\3') AS DEPTNO_LIST3
     , REGEXP_REPLACE(LISTAGG(DEPTNO, '/') WITHIN GROUP(ORDER BY DEPTNO), '([^/]+)(/\1)*(/|$)', '\1\3') AS DEPTNO_LIST4
     , REGEXP_REPLACE(LISTAGG(DEPTNO, '~') WITHIN GROUP(ORDER BY DEPTNO), '([^~]+)(~\1)*(~|$)', '\1\3') AS DEPTNO_LIST5
     , REGEXP_REPLACE(LISTAGG(DEPTNO, '-') WITHIN GROUP(ORDER BY DEPTNO), '([^-]+)(-\1)*(-|$)', '\1\3') AS DEPTNO_LIST6
     , REGEXP_REPLACE(LISTAGG(DEPTNO, '_') WITHIN GROUP(ORDER BY DEPTNO), '([^_]+)(_\1)*(_|$)', '\1\3') AS DEPTNO_LIST7
    FROM EMP
    WHERE JOB IN ('MANAGER','SALESMAN','CLERK','ANALYST')
    GROUP BY JOB

LISTAGG 중복 제거 후 합치기 (구분자 변경 예시)

 

정규식을 이용하여 중복된 값을 제거할 때 구분자를 변경하고 싶다면 LISTAGG 함수 안에 있는 구분자만 변경하면 중복제거가 되지 않습니다. 위 예시처럼 정규식 안에 있는 구분자도 변경해야 중복 제거가 적용되니 참고하시기 바랍니다.

 

LISTAGG 중복 제거 후 합치기 (엔터)

SELECT JOB
     , REGEXP_REPLACE(LISTAGG(DEPTNO, ',') WITHIN GROUP(ORDER BY DEPTNO), '([^,]+)(,\1)*(,|$)', '\1\3') AS DEPTNO_LIST
     /* 구분자를 엔터로 바꾸기 */
     , REPLACE(REGEXP_REPLACE(LISTAGG(DEPTNO, ',') WITHIN GROUP(ORDER BY DEPTNO), '([^,]+)(,\1)*(,|$)', '\1\3'),',',CHR(10)||CHR(13)) AS DEPTNO_LIST1
     /* 구분자를 <BR>로 바꾸기 (HTML 에서 인식하는 엔터) */
     , REGEXP_REPLACE(LISTAGG(DEPTNO, '<BR>') WITHIN GROUP(ORDER BY DEPTNO), '([^<BR>]+)(<BR>\1)*(<BR>|$)', '\1\3') AS DEPTNO_LIST2
    FROM EMP
    WHERE JOB IN ('MANAGER','SALESMAN','CLERK','ANALYST')
    GROUP BY JOB

LISTAGG 구분자를 엔터로 변환하는 방법

 

일반적인 구분자로 값을 이어 붙이면 가독성이 떨어질 수 있어 엔터를 이용해 값을 표시하면 깔끔해 보일 수 있습니다.

오라클과 일반적인 프로그램에서는 CHR(10)||CHR(13) 을 엔터로 인식하지만, 웹 개발 시 <BR> 태그를 엔터로 인식합니다.

 

<BR>은 일반적인 구분자처럼 사용하면 중복 제거 기능이 동작하지만, CHR(10)||CHR(13) 은 적용되지 않아 일반적인 구분자로 LISTAGG 함수 실행 후 REPLACE 함수로 구분자를 CHR(10)||CHR(13) 로 변경하면 됩니다.