[Oracle]여러로우 한줄로 붙이기(LISTAGG)

엑셀로 생각을하면 여러줄에 있는 결과값을
CONCATENATE함수를 써서 구분자를 추가하여 붙여넣는데 오라클도 같은 기능이있다.
오라클 11g에서는 wm_concat이 있으나19c에서는 사용 불가? 하기때문에 오라클 버전이 변경되면 해당함수를 찾아 변경해야한다.
listagg를 몰랐을땐 피벗으로 좌표값처럼 가져와서 붙였는데 너무편한기능이지만 아마 오라클9i부터 지원하는 것으로 알고있어서 옛유물사이트에선 지원하지 않을수도 있으니 db버전을 확인하자.

그럼 사용법은

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

WITH TAB AS (
SELECT '10' COL FROM DUAL
UNION ALL
SELECT '20' COL FROM DUAL
UNION ALL
SELECT '30' COL FROM DUAL)
SELECT LISTAGG(COL, ',') WITHIN GROUP (ORDER BY COL) FROM TAB;​

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

 
이렇게 10,20,30 인 데이터 COL 3로우가있을때,

COL

10

20

30

 

WITH TAB AS (
SELECT '10' COL FROM DUAL
UNION ALL
SELECT '20' COL FROM DUAL
UNION ALL
SELECT '30' COL FROM DUAL)
SELECT LISTAGG(COL, ',') WITHIN GROUP (ORDER BY COL) FROM TAB;

LISTAGG( ) 함수는 그룹 함수이기 때문에 GROUP BY 또는 PARTITION BY 절과 함께 사용해야 한다.
WM_CONCAT 함수는 DISTINCT를 사용할 수 있으나, LISTAGG 함수는 사용할 수 없다. 그러나 정규식 함수를 사용하여 중복을 제거할 수 있다.
 
 

컬럼 합치기

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

중복을 제거하여 컬럼 합치기(정규식사용)

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)