OracleのLISTAGGでDISTINCTを使いたかったがダメだった

##[1] 入社年月が同一である従業員を同一行に並べる
###SQL
```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
```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

```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

```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オプションが使えません。
もう少し簡潔な方法はないのかな？

```sql
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|