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

예를 들어 아래 사원 테이블을 보죠.
SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP;
cs

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




10번 부서에 속한 사원 목록
여기서 10번 부서의 사원명을 구해봅니다.
SELECT DEPTNO, ENAME FROM EMP WHERE DEPTNO = 10;
cs

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



각 부서에 속한 사원 목록
이번에는 10번부서 뿐만 아니라 각 부서별 사원명을 출력해 보도록 하겠습니다. GROUP BY를 사용할 수 있을것입니다.
SELECT DEPTNO, ENAME
FROM EMP
GROUP BY DEPTNO, ENAME
ORDER BY DEPTNO, ENAME;
cs



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


LISTAGG 사용 형식
LISTAGG 사용 형식은 아래와 같습니다.
SELECT
       LISTAGG(필드명, 구분자)
       WITHIN GROUP ( ORDER BY 정렬기준필드 ASC 또는 DESC)
FROM 테이블
cs

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


LISTAGG 함수를 사용해봅시다.
SELECT
       DEPTNO,
       LISTAGG(ENAME, ', ')
       WITHIN GROUP ( ORDER BY ENAME DESC)
       AS ENAMES
FROM EMP
GROUP BY DEPTNO;
cs

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



만약 사원명을 거꾸로 정렬하고 싶다면 아래와 같이 WITHIN GROUP를 통해 정렬 기준을 변경할 수 있습니다.
SELECT
       DEPTNO,
       LISTAGG(ENAME, ', ')
       WITHIN GROUP ( ORDER BY ENAME ASC)
       AS ENAMES
FROM EMP
GROUP BY DEPTNO;
cs




LISTAGG함수 실행 결과에서 중복 제거하기
직책별로 어떤 부서들이 있는지 출력해봅니다.
SELECT JOB, DEPTNO, ENAME
FROM EMP
ORDER BY JOB, DEPTNO;
cs

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


이번에는 GROUP BY를 통해 그룹화함과 동시에 LISTAGG를 통해 한행으로 표현해 봅니다.
SELECT JOB,
       LISTAGG(DEPTNO, ', ')
       WITHIN GROUP ( ORDER BY DEPTNO ASC)
       AS DEPTS
FROM EMP
GROUP BY JOB;
cs


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


중복 해결은 의외로 간단한데 중복이 제거된 상태의 뷰를 만들고 그 뷰를 가지고 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;
cs


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






블로그 이미지

도로락

IT, 프로그래밍, 컴퓨터 활용 정보 등을 위한 블로그

,