[1] 入社年月が同一である従業員を同一行に並べる
SQL
SELECT e.empno AS empno,
e.ename AS ename,
TO_CHAR(e.hiredate, 'YYYY/MM') AS hire_ym,
LISTAGG(same_ym.ename, ',')
WITHIN GROUP (ORDER BY same_ym.empno) AS same_ym_list
FROM emp e
INNER JOIN emp same_ym
ON TO_CHAR(same_ym.hiredate, 'YYYY/MM') =
TO_CHAR(e.hiredate, 'YYYY/MM')
GROUP BY e.empno,
e.ename,
TO_CHAR(e.hiredate, 'YYYY/MM')
ORDER BY e.empno
結果
| EMPNO | ENAME | HIRE_YM | SAME_YM_LIST |
|---|---|---|---|
| 7369 | SMITH | 1980/12 | SMITH |
| 7499 | ALLEN | 1981/02 | ALLEN,WARD |
| 7521 | WARD | 1981/02 | ALLEN,WARD |
| 7566 | JONES | 1981/04 | JONES |
| 7654 | MARTIN | 1981/09 | MARTIN,TURNER |
| 7698 | BLAKE | 1981/05 | BLAKE |
| 7782 | CLARK | 1981/06 | CLARK |
| 7839 | KING | 1981/11 | KING |
| 7844 | TURNER | 1981/09 | MARTIN,TURNER |
| 7900 | JAMES | 1981/12 | JAMES,FORD |
| 7902 | FORD | 1981/12 | JAMES,FORD |
| 7934 | MILLER | 1982/01 | MILLER |
[2] 所属部門が同一である従業員を同一行に並べる
SQL
SELECT e.empno AS empno,
e.ename AS ename,
d.deptno AS deptno,
d.dname AS dname,
LISTAGG(same_dept.ename, ',')
WITHIN GROUP (ORDER BY same_dept.empno) AS same_dept_list
FROM emp e
INNER JOIN dept d
ON d.deptno = e.deptno
INNER JOIN emp same_dept
ON same_dept.deptno = e.deptno
GROUP BY e.empno,
e.ename,
d.deptno,
d.dname
ORDER BY e.empno
結果
| EMPNO | ENAME | DEPTNO | DNAME | SAME_DEPT_LIST |
|---|---|---|---|---|
| 7369 | SMITH | 20 | RESEARCH | SMITH,JONES,FORD |
| 7499 | ALLEN | 30 | SALES | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7521 | WARD | 30 | SALES | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7566 | JONES | 20 | RESEARCH | SMITH,JONES,FORD |
| 7654 | MARTIN | 30 | SALES | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7698 | BLAKE | 30 | SALES | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7782 | CLARK | 10 | ACCOUNTING | CLARK,KING,MILLER |
| 7839 | KING | 10 | ACCOUNTING | CLARK,KING,MILLER |
| 7844 | TURNER | 30 | SALES | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7900 | JAMES | 30 | SALES | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7902 | FORD | 20 | RESEARCH | SMITH,JONES,FORD |
| 7934 | MILLER | 10 | ACCOUNTING | CLARK,KING,MILLER |
[3] 入社年月が同一である従業員と、所属部門が同一である従業員をそれぞれ同一行に並べる([1]と[2]の組合せ)
SQL
SELECT e.empno,
e.ename,
TO_CHAR(e.hiredate, 'YYYY/MM') AS hire_ym,
e.deptno,
d.dname,
e_ym.same_ym_list,
e_dept.same_dept_list
FROM emp e
INNER JOIN dept d
ON d.deptno = e.deptno
INNER JOIN (SELECT e.empno AS empno,
LISTAGG(same_ym.ename, ',')
WITHIN GROUP (ORDER BY same_ym.empno) AS same_ym_list
FROM emp e
INNER JOIN emp same_ym
ON TO_CHAR(same_ym.hiredate, 'YYYY/MM') =
TO_CHAR(e.hiredate, 'YYYY/MM')
GROUP BY e.empno) e_ym
ON e_ym.empno = e.empno
INNER JOIN (SELECT e.empno AS empno,
LISTAGG(same_dept.ename, ',')
WITHIN GROUP (ORDER BY same_dept.empno) AS same_dept_list
FROM emp e
INNER JOIN emp same_dept
ON same_dept.deptno = e.deptno
GROUP BY e.empno) e_dept
ON e_dept.empno = e.empno
ORDER BY e.empno
結果
| EMPNO | ENAME | HIRE_YM | DEPTNO | DNAME | SAME_YM_LIST | SAME_DEPT_LIST |
|---|---|---|---|---|---|---|
| 7369 | SMITH | 1980/12 | 20 | RESEARCH | SMITH | SMITH,JONES,FORD |
| 7499 | ALLEN | 1981/02 | 30 | SALES | ALLEN,WARD | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7521 | WARD | 1981/02 | 30 | SALES | ALLEN,WARD | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7566 | JONES | 1981/04 | 20 | RESEARCH | JONES | SMITH,JONES,FORD |
| 7654 | MARTIN | 1981/09 | 30 | SALES | MARTIN,TURNER | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7698 | BLAKE | 1981/05 | 30 | SALES | BLAKE | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7782 | CLARK | 1981/06 | 10 | ACCOUNTING | CLARK | CLARK,KING,MILLER |
| 7839 | KING | 1981/11 | 10 | ACCOUNTING | KING | CLARK,KING,MILLER |
| 7844 | TURNER | 1981/09 | 30 | SALES | MARTIN,TURNER | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7900 | JAMES | 1981/12 | 30 | SALES | JAMES,FORD | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7902 | FORD | 1981/12 | 20 | RESEARCH | JAMES,FORD | SMITH,JONES,FORD |
| 7934 | MILLER | 1982/01 | 10 | ACCOUNTING | MILLER | CLARK,KING,MILLER |
[4] ダメな例
SQL
SELECT e.empno AS empno,
e.ename AS ename,
TO_CHAR(e.hiredate, 'YYYY/MM') AS hire_ym,
d.deptno AS deptno,
d.dname AS dname,
LISTAGG(same_ym.ename, ',')
WITHIN GROUP (ORDER BY same_ym.empno) AS same_ym_list,
LISTAGG(same_dept.ename, ',')
WITHIN GROUP (ORDER BY same_dept.empno) AS same_dept_list
FROM emp e
INNER JOIN dept d
ON d.deptno = e.deptno
INNER JOIN emp same_ym
ON TO_CHAR(same_ym.hiredate, 'YYYY/MM') =
TO_CHAR(e.hiredate, 'YYYY/MM')
INNER JOIN emp same_dept
ON same_dept.deptno = e.deptno
GROUP BY e.empno,
e.ename,
TO_CHAR(e.hiredate, 'YYYY/MM'),
d.deptno,
d.dname
ORDER BY e.empno
結果
| EMPNO | ENAME | HIRE_YM | DEPTNO | DNAME | SAME_YM_LIST | SAME_DEPT_LIST |
|---|---|---|---|---|---|---|
| 7369 | SMITH | 1980/12 | 20 | RESEARCH | SMITH,SMITH,SMITH | SMITH,JONES,FORD |
| 7499 | ALLEN | 1981/02 | 30 | SALES | ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,WARD,WARD,WARD,WARD,WARD,WARD | ALLEN,ALLEN,WARD,WARD,MARTIN,MARTIN,BLAKE,BLAKE,TURNER,TURNER,JAMES,JAMES |
| 7521 | WARD | 1981/02 | 30 | SALES | ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,WARD,WARD,WARD,WARD,WARD,WARD | ALLEN,ALLEN,WARD,WARD,MARTIN,MARTIN,BLAKE,BLAKE,TURNER,TURNER,JAMES,JAMES |
| 7566 | JONES | 1981/04 | 20 | RESEARCH | JONES,JONES,JONES | SMITH,JONES,FORD |
| 7654 | MARTIN | 1981/09 | 30 | SALES | MARTIN,MARTIN,MARTIN,MARTIN,MARTIN,MARTIN,TURNER,TURNER,TURNER,TURNER,TURNER,TURNER | ALLEN,ALLEN,WARD,WARD,MARTIN,MARTIN,BLAKE,BLAKE,TURNER,TURNER,JAMES,JAMES |
| 7698 | BLAKE | 1981/05 | 30 | SALES | BLAKE,BLAKE,BLAKE,BLAKE,BLAKE,BLAKE | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
| 7782 | CLARK | 1981/06 | 10 | ACCOUNTING | CLARK,CLARK,CLARK | CLARK,KING,MILLER |
| 7839 | KING | 1981/11 | 10 | ACCOUNTING | KING,KING,KING | CLARK,KING,MILLER |
| 7844 | TURNER | 1981/09 | 30 | SALES | MARTIN,MARTIN,MARTIN,MARTIN,MARTIN,MARTIN,TURNER,TURNER,TURNER,TURNER,TURNER,TURNER | ALLEN,ALLEN,WARD,WARD,MARTIN,MARTIN,BLAKE,BLAKE,TURNER,TURNER,JAMES,JAMES |
| 7900 | JAMES | 1981/12 | 30 | SALES | JAMES,JAMES,JAMES,JAMES,JAMES,JAMES,FORD,FORD,FORD,FORD,FORD,FORD | ALLEN,ALLEN,WARD,WARD,MARTIN,MARTIN,BLAKE,BLAKE,TURNER,TURNER,JAMES,JAMES |
| 7902 | FORD | 1981/12 | 20 | RESEARCH | JAMES,JAMES,JAMES,FORD,FORD,FORD | SMITH,SMITH,JONES,JONES,FORD,FORD |
| 7934 | MILLER | 1982/01 | 10 | ACCOUNTING | MILLER,MILLER,MILLER | CLARK,KING,MILLER |
残念ながらLISTAGGではDISTINCTオプションが使えません。
もう少し簡潔な方法はないのかな？
LISTAGG(DISTINCT same_ym.ename, ',') WITHIN GROUP (ORDER BY same_ym.empno)
結果
ORA-30482: この機能にDISTINCTオプションは使用できません
参考
使用したテーブル（Oracleのサンプルスキーマ）
EMP
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980/12/17 | 800 | 20 | |
| 7499 | ALLEN | SALESMAN | 7698 | 1981/02/20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981/02/22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981/04/02 | 2975 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 1981/09/28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981/05/01 | 2850 | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 1981/06/09 | 2450 | 10 | |
| 7839 | KING | PRESIDENT | 1981/11/17 | 5000 | 10 | ||
| 7844 | TURNER | SALESMAN | 7698 | 1981/09/08 | 1500 | 0 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981/12/03 | 950 | 30 | |
| 7902 | FORD | ANALYST | 7566 | 1981/12/03 | 3000 | 20 | |
| 7934 | MILLER | CLERK | 7782 | 1982/01/23 | 1300 | 10 |
DEPT
| DEPTNO | DNAME | LOC |
|---|---|---|
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |