はじめに

MySQLにはgroup_concat()という関数があり、GROUP BYの結果セットをカンマなどで連結して取得することができます。
Oracleに同じような機能がないかと調べてみた所、LISTAGG()という関数があることが分かったので、試しに使ってみました。

使用した環境

Oracle社が提供しているOracle Live SQLでOracle19cを利用しました。

作成した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