[Oracle] 여러행의 데이터를 하나의 행에 가로로 출력하기 LISTAGG 함수 사용법

여러행의 데이터를 하나의 행에 가로로 출력하기 LISTAGG 함수 사용법

여러행의 데이터를 하나의 행에 가로로 출력이 가능합니다.

예를 들어 아래 사원 테이블을 보죠.

SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP;

사원원호(EMPNO), 사원명(ENAME), 직책(JOB), DEPTNO(부서번호)가 있습니다.

10번 부서에 속한 사원 목록

여기서 10번 부서의 사원명을 구해봅니다.

SELECT DEPTNO, ENAME FROM EMP WHERE DEPTNO = 10;

10번 부서에 속해있는 사원이 세명이기에 세개의 행이 출력되었습니다.

각 부서에 속한 사원 목록

이번에는 10번부서 뿐만 아니라 각 부서별 사원명을 출력해 보도록 하겠습니다. GROUP BY를 사용할 수 있을것입니다.

SELECT DEPTNO, ENAME
FROM EMP
GROUP BY DEPTNO, ENAME
ORDER BY DEPTNO, ENAME;

LISTAGG 함수 이용하기

그런데 만약 각 부서별로 속해있는 사원을 한 행으로 출력하고 싶으면 어떻게 해야 할까요? 이때 사용할 수 있는 함수가 LISTAGG입니다. 

LISTAGG 사용 형식

LISTAGG 사용 형식은 아래와 같습니다.

SELECT
       LISTAGG(필드명, 구분자)
       WITHIN GROUP ( ORDER BY 정렬기준필드 ASC 또는 DESC)
FROM 테이블

LISTAGG에 합칠 필드명과, 합칠 때 사용할 구분자를 지정해주고 WITHIN GROUP 부분에 합칠때 정렬 기준을 정하면 됩니다.

LISTAGG 함수를 사용해봅시다.

SELECT
       DEPTNO,
       LISTAGG(ENAME, ', ')
       WITHIN GROUP ( ORDER BY ENAME DESC)
       AS ENAMES
FROM EMP
GROUP BY DEPTNO;

LISTAGG함수는 그룹화 한 상태에서 각 그룹안에 특정 필드값을 한 행으로 출력이 가능합니다. 결과는 아래와 같습니다.

만약 사원명을 거꾸로 정렬하고 싶다면 아래와 같이 WITHIN GROUP를 통해 정렬 기준을 변경할 수 있습니다.

SELECT
       DEPTNO,
       LISTAGG(ENAME, ', ')
       WITHIN GROUP ( ORDER BY ENAME ASC)
       AS ENAMES
FROM EMP
GROUP BY DEPTNO;

LISTAGG함수 실행 결과에서 중복 제거하기

직책별로 어떤 부서들이 있는지 출력해봅니다.

SELECT JOB, DEPTNO, ENAME
FROM EMP
ORDER BY JOB, DEPTNO;

ENAME은 참고용으로 출력한 것인데, SALESMAN의 경우에는 네명의 사원이 있는데, 모두 30번 부서인것을 알 수 있습니다.

이번에는 GROUP BY를 통해 그룹화함과 동시에 LISTAGG를 통해 한행으로 표현해 봅니다.

SELECT JOB,
       LISTAGG(DEPTNO, ', ')
       WITHIN GROUP ( ORDER BY DEPTNO ASC)
       AS DEPTS
FROM EMP
GROUP BY JOB;

앞서 보았듯 5번행의 SALESMAN의 경우에는 네 개의 중복된 부서번호가 있습니다. 안타깝게도 LISTAGG는 DISTINCT를 사용할 수 없기 때문에 다른 방법이 필요합니다.

중복 해결은 의외로 간단한데 중복이 제거된 상태의 뷰를 만들고 그 뷰를 가지고 LISTAGG를 사용하는 것 입니다.

SELECT JOB,
       LISTAGG(DEPTNO, ', ')
       WITHIN GROUP ( ORDER BY DEPTNO)
       AS DEPTS
FROM (
         SELECT JOB, DEPTNO
         FROM EMP
         GROUP BY JOB, DEPTNO
     ) A
GROUP BY JOB;

서브쿼리에서 이미 DEPTNO의 중복을 제거한 상태에서 LISTAGG를 사용하기 때문에 중복이 제거되었습니다.