[Oracle] LISTAGG WITHIN GROUP

ORACLE LISTAGG 여러 행을 하나의 컬럼으로 가져오기

11g 에서 추가. 10g 이하는 WM_CONCAT 함수 사용

(WM_CONCAT은 페이지 하단 Link 참고)

오라클에서 여러개의 열로 된 값을 한 행의 값으로 가져와야 할 때 LISTAGG 함수를 사용한다.

-  사용방법

SELECT LISTAGG(가져올컬럼, 구분자) WITHIN GROUP (ORDER BY 순서컬럼)
  FROM TABLE_NM

아래의 예를 보자

예)

SELECT DRIVER_NM
  FROM DRIVER_INFO;

 

DRIVER_INFO 테이블에서 DRIVER_NM 컬럼을 가져왔다.

위와 같은 여러 열의 값을 LISTAGG를 사용하여 하나의 열로 가져와 보자.

SELECT LISTAGG(DRIVER_NM, ',') WITHIN GROUP (ORDER BY DRIVER_NM) AS DRIVER_NM
  FROM DRIVER_INFO

 

DRIVER_NM 컬럼의 값을 오름차순 그룹으로 묶어 하나의 열로 표현 했다.

그렇다면 LISTAGG의 중복을 제외하기 위해선 어떻게 해야 할까?

아쉽게도 LISTAGG는 중복제외(DISTINCT)를 지원하지 않는다.

SELECT DISTINCT(LISTAGG(DRIVER_NM, ',') WITHIN GROUP (ORDER BY DRIVER_NM)) AS DRIVER_NM
  FROM DRIVER_INFO

위와 같이 DISTINCT를 사용하여도 오류는 나지 않지만

기존에 사용하지 않은 쿼리와 같은 값을 조회한다.

그래서 이를 해결하기 위해

아래와 같이 서브쿼리를 사용하여 중복을 제외한 테이블에서 LISTAGG를 사용한다.

SELECT LISTAGG(DRIVER_NM, ',') WITHIN GROUP (ORDER BY DRIVER_NM) AS DRIVER_NM
  FROM (SELECT DISTINCT DRIVER_NM FROM DRIVER_INFO)

참조)

다른 컬럼과 같이 GROUPING 된 LISTAGG 를 조회 하려면 PARTITION BY를 사용한다.

SELECT DRIVER_ID
     , LISTAGG(DRIVER_NM, ',') WITHIN GROUP (ORDER BY DRIVER_NM)
          OVER(PARTITION BY DRIVER_ID)  AS DRIVER_NM
  FROM DRIVER_INFO