[ORACLE] LISTAGG 집계함수 (여러 행을 하나의 값으로 합치기)

• 오라클에서 여러 행의 컬럼 값을 하나로 합쳐주는 기능을 지원하는 함수는 버전별로 상이하다.
  
  • XMLAGG : 오라클 9i 이하 
  • WM_CONCAT : 오라클 10g ~ 11g R1
  • LISTAGG : 오라클 11g R2 ~ ( DISTINCT 를 사용한 중복제거 불가 / 정규식 함수 사용 )
  • LISTAGG : 오라클 19g ~  ( DISTINCT 를 사용한 중복제거 가능 )
• LISTAGG( ) 함수는 그룹 함수이기 때문에 GROUP BY 또는 PARTITION BY 절과 함께 사용해야 한다.

 

구문 예시

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

 

 

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')

 

 

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

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

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

 

 

 

 

 

Tmax Tibero의 경우에는 AGGR_CONCAT 함수를 사용하면 되는데, 설명은 아래 블로그를 참고하면 된다.

[Tibero] AGGR_CONCAT 함수 (여러 행을 하나의 값으로 합치기)

 

 

 

 

 

[ 참고 ]

https://gent.tistory.com/328