[Oracle] 오라클 LISTAGG 함수 사용법 (중복제거, 정렬, 컬럼 합치기)

오라클에서 여러 행의 컬럼 값을 하나로 합치기 위해서는 XMLAGG(오라클 9i 이하), WM_CONCAT(오라클 10g~11g R1) 함수를 사용하였다. 오라클 11g R2 버전부터 WM_CONCAT 함수를 사용을 할 수 없으며 LISTAGG 함수를 사용해야 한다. (WM_CONCAT, XMLAGG 사용법)

 

LISTAGG( ) 함수는 그룹 함수이기 때문에 GROUP BY 또는 PARTITION BY 절과 함께 사용해야 한다.

WM_CONCAT 함수는 DISTINCT를 사용할 수 있으나, LISTAGG 함수는 사용할 수 없다. 그러나 정규식 함수를 사용하여 중복을 제거할 수 있으며 ORDER BY 절을 사용할 수 있어서 값의 정렬이 가능하다.

 

LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명])

 

컬럼 합치기

SELECT LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS names
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

GROUP BY 절을 사용하여 컬럽 합치기

SELECT job
     , LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS names
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 GROUP BY job

 

PARTITION BY 절을 사용하여 컬럼 합치기

SELECT ename
     , job
     , LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) OVER(PARTITION BY job) AS names
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

PARTITION BY 절을 사용하면 조회된 행을 그대로 유지하면서 합쳐진 컬럼의 값을 표시할 수 있다.

 

중복을 제거하여 컬럼 합치기

SELECT job
     , REGEXP_REPLACE(LISTAGG(deptno, ',') WITHIN GROUP(ORDER BY deptno), '([^,]+)(,\1)*(,|$)', '\1\3') deptnos
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN', 'CLERK')
 GROUP BY job

 

REGEXP_REPLACE 정규식 함수를 사용하여 컬럼의 중복을 제거하는 방법이며, 값의 순서로 정렬되어 있어야 정확한 중복제거가 된다. (ORDER BY deptno)

 

오라클 19c 이상부터 DISTINCT를 사용하여 중복을 제거할 수 있다.

LISTAGG(DISTINCT deptno, ',') WITHIN GROUP(ORDER BY deptno)

 

사용 예제

 

 

 

 

 

사용_예제_쿼리.txt

0.01MB

 

※ 정규식 참고

SELECT REGEXP_REPLACE('10,10,10,10,20,30,40,50', '([^,]+)(,\1)*(,|$)', '\1\3') AS val
     , REGEXP_SUBSTR('10,10,10,10,20,30,40,50', '([^,]+)') AS "\1"
     , REGEXP_SUBSTR('10,10,10,10,20,30,40,50', '([^,]+)(,\1)*') AS "\2"
     , REGEXP_SUBSTR('10,10,10,10,20,30,40,50', '(,|$)') AS "\3"
  FROM dual