오라클 행열 전환(1) - LISTAGG

행 데이터를 열 데이터로 전환

 

SELECT empno, ename, job, sal FROM emp;

EMPNO	ENAME	JOB	SAL
7839	KING	PRESIDENT	5000
7698	BLAKE	MANAGER	2850
7782	CLARK	MANAGER	2450
7566	JONES	MANAGER	2975
7788	SCOTT	ANALYST	3000
7902	FORD	ANALYST	3000
7369	SMITH	CLERK	800
7499	ALLEN	SALESMAN	1600
7521	WARD	SALESMAN	1250
7654	MARTIN	SALESMAN	1250
7844	TURNER	SALESMAN	1500
7876	ADAMS	CLERK	1100
7900	JAMES	CLERK	950
7934	MILLER	CLERK	1300

 

-- JOB으로 그룹핑 하되, 해당 JOB에 포함하는 ENAME은 ','로 구분하여 가로출력
SELECT JOB
     , LISTAGG(ENAME,',') WITHIN GROUP (ORDER BY ENAME) ENAME
     , COUNT(1) CNT
FROM EMP
GROUP BY JOB
;

JOB	ENAME	CNT
ANALYST	FORD,SCOTT	2
CLERK	ADAMS,JAMES,MILLER,SMITH	4
MANAGER	BLAKE,CLARK,JONES	3
PRESIDENT	KING	1
SALESMAN	ALLEN,MARTIN,TURNER,WARD	4