はじめに
MySQLにはgroup_concat()という関数があり、GROUP BYの結果セットをカンマなどで連結して取得することができます。
Oracleに同じような機能がないかと調べてみた所、LISTAGG()という関数があることが分かったので、試しに使ってみました。
使用した環境
作成したSQL
- テストデータを投入するのが面倒だったので、今回はサンプルスキーマ（SCOTT）のDEPTテーブルとEMPテーブルを使いました。
- DEPTテーブルには紐づく社員の居ない部署（OPERATIONS）があるため、
LEFT OUTER JOINでEMPテーブルと結合させています。
サンプルコード1
-
Oracle19cのLISTAGG関数のリファレンスなどを参考にして、
LISTAGG()を使ってみました。
- MySQLの
group_concat()に比べると、少々構文が長い印象です。
-
LISTAGG()のリファレンスには、以下の注意書きがありました。
- 列のデータ型がRAW型の時は戻り値がRAW型、それ以外の時は戻り値がVARCHAR2型となる。
- 戻り値がVARCHAR2型の場合、MAX_STRING_SIZEの設定値によって戻り値のデータの最大長が変わる。
SELECT
T1.DNAME AS DEPT_NAME,
LISTAGG(T2.ENAME, ',') WITHIN GROUP (ORDER BY T2.ENAME ASC) AS DEPT_MEMBERS
FROM
SCOTT.DEPT T1
LEFT OUTER JOIN SCOTT.EMP T2
ON T1.DEPTNO = T2.DEPTNO
GROUP BY
T1.DNAME;
サンプルコード1の実行結果
- 部署に所属するメンバーが、
DEPT_MEMBERS列にカンマ区切りで出力されています。
| DEPT_NAME |
DEPT_MEMBERS |
| ACCOUNTING |
CLARK,KING,MILLER |
| OPERATIONS |
- |
| RESEARCH |
ADAMS,FORD,JONES,SCOTT,SMITH |
| SALES |
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD |
サンプルコード2
- Oracle19cからは、
LISTAGG()に渡す結果セットの重複を除去できるDISTINCTというキーワードが追加されたそうなので、それを踏まえたSQLを作ってみました。
-
Oracle18cのリファレンスだと
DISTINCTというキーワードが見当たらないので、おそらく19cから追加されたものと思われます。
SELECT
T1.DNAME AS DEPT_NAME,
LISTAGG(DISTINCT T2.JOB, ',') WITHIN GROUP (ORDER BY T2.JOB ASC) AS JOBS
FROM
SCOTT.DEPT T1
LEFT OUTER JOIN SCOTT.EMP T2
ON T1.DEPTNO = T2.DEPTNO
GROUP BY
T1.DNAME;
サンプルコード2の実行結果
- 部署に所属するメンバーの役職が、
JOBS列にカンマ区切りで出力されています。
- 実際のデータにはRESEARCH部にはANALYSTが2名、SALES部にはSALESMANが4名在籍していますが、
DISTINCTキーワードで結果の重複が除去されています。
| DEPT_NAME |
JOBS |
| ACCOUNTING |
CLERK,MANAGER,PRESIDENT |
| OPERATIONS |
- |
| RESEARCH |
ANALYST,CLERK,MANAGER |
| SALES |
CLERK,MANAGER,SALESMAN |
参考URL
