Oracle SQLのLISTAGG関数で複数行を1行に連結する方法｜区切り文字・GROUP BY・並び順・重複除外まで解説

Oracle Databaseで、同じグループに属する複数行の文字列を「A,B,C」のように1行へまとめたい場合に便利なのがLISTAGG関数です。LISTAGGを使うと、複数行の値を指定した順序で並べ、カンマやスラッシュなど任意の区切り文字を挟みながら1つの文字列として取得できます。

例えば、部署ごとに複数の社員名が登録されているテーブルから、「営業部｜田中,佐藤,鈴木」のような結果を作ることができます。単に連結するだけでなく、WITHIN GROUPのORDER BYによる並び順の指定、GROUP BYとの組み合わせ、重複値の除外、長すぎる結果への対処なども重要です。

結論として、Oracle SQLのLISTAGGは「グループ化された複数行の文字列を、指定した区切り文字で1行に連結する」という用途に適した関数です。ただし、使い方によって結果や注意点が変わるため、基本構文から順番に確認していきましょう。

OracleのLISTAGG関数とは？

LISTAGGは、複数の行から取得した値を1つの文字列へまとめるためのOracle DatabaseのSQL関数です。集計関数としてGROUP BYと組み合わせれば、グループ単位で文字列を連結できます。

基本的な考え方は「どの列を連結するか」「何を区切り文字にするか」「どの順番で連結するか」の3点です。

LISTAGG(連結する列, '区切り文字') WITHIN GROUP (ORDER BY 並び順を決める列)

Oracle公式ドキュメントでも、LISTAGGは指定された順序に従ってmeasure_exprの値を並べ、delimiterで区切って連結する関数として説明されています。Oracle Database SQL Language Reference「LISTAGG」[参照]

LISTAGGで複数行をカンマ区切りの1行にする基本例

例えば、EMPLOYEEというテーブルに部署番号と社員名が保存されているとします。

部署ごとに社員名をカンマ区切りで1行へまとめる場合は、次のようなSQLを書けます。

SELECT department_id, LISTAGG(employee_name, ',') WITHIN GROUP (ORDER BY employee_name) AS employee_list FROM employee GROUP BY department_id;

イメージとしては、次のような結果になります。

このように、GROUP BYでグループを作り、そのグループ内に存在する複数行の値をLISTAGGで連結するのが代表的な利用方法です。

区切り文字はカンマ以外でも指定できる

LISTAGGの2番目の引数には区切り文字を指定できます。カンマに限定されているわけではありません。

例えば「 / 」で区切りたい場合は次のようにします。

LISTAGG(employee_name, ' / ') WITHIN GROUP (ORDER BY employee_name)

結果は「佐藤 / 鈴木 / 田中」のようになります。

縦棒を使いたければ次のようにできます。

LISTAGG(employee_name, ' | ') WITHIN GROUP (ORDER BY employee_name)

CSVに近い表示ならカンマ、画面表示なら「 / 」や「・」など、利用目的に応じて区切り文字を選べます。

WITHIN GROUPのORDER BYが重要な理由

LISTAGGでは、単に文字列を連結するだけではなく、どの順番で連結するかをWITHIN GROUPのORDER BYで指定します。

例えば社員番号順に社員名を連結したいのであれば、次のように書けます。

SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_id) AS employee_list FROM employee GROUP BY department_id;

この場合、employee_nameの五十音順ではなくemployee_idの順番で名前が連結されます。

なお、Oracle公式ドキュメントでは、ORDER BYの列によって一意の順序が決まる場合にのみ、LISTAGGの結果が決定的になると説明されています。並び順を確実に固定したい場合は、一意に順番が決まる列をORDER BYへ含めることがポイントです。Oracle LISTAGG仕様[参照]

GROUP BYとLISTAGGの役割は別

初めてLISTAGGを使用するときに混乱しやすいのがGROUP BYとの関係です。GROUP BYは「どの単位で結果をまとめるか」を決め、LISTAGGは「そのグループに存在する複数の値をどう連結するか」を担当します。

例えば会社の注文データにCUSTOMER_IDとPRODUCT_NAMEがある場合、顧客ごとの購入商品一覧を作るなら次のようなSQLになります。

SELECT customer_id, LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS products FROM orders GROUP BY customer_id;

CUSTOMER_IDが100の注文が「りんご」「みかん」「バナナ」の3行に分かれていれば、それらを「りんご, みかん, バナナ」のような1つの値へ集約できます。

LISTAGGではNULLはどう扱われる？

連結対象の列にNULLが含まれているケースもあります。OracleのLISTAGGでは、measure_exprにおけるNULL値は無視されます。

例えば値が「東京」「NULL」「大阪」の3行だった場合、NULLを文字列の「NULL」として自動的に挿入して「東京,NULL,大阪」とするわけではありません。

NULLにも特定の文字を表示したい場合には、NVLなどを利用して明示的に置き換える方法があります。

LISTAGG(NVL(city_name, '未設定'), ', ') WITHIN GROUP (ORDER BY city_id)

このようにすれば、NULLを「未設定」という文字列として連結対象に含められます。

重複した値を1回だけ表示したい場合はDISTINCT

実際のデータでは、同じ値が複数行に存在する場合があります。例えば購入履歴から商品カテゴリを一覧化すると、「食品,食品,家電,食品」のような結果になることがあります。

対応するOracle Databaseのバージョンでは、LISTAGGにDISTINCTを指定することで重複値を取り除けます。

SELECT customer_id, LISTAGG(DISTINCT category_name, ', ') WITHIN GROUP (ORDER BY category_name) AS categories FROM orders GROUP BY customer_id;

これなら同じカテゴリ名が複数行に存在していても、「家電, 食品」のようにまとめられます。

ただし、LISTAGG DISTINCTの利用可否はOracle Databaseのバージョンによって異なります。古いOracle環境を扱っている場合は、そのバージョンの公式マニュアルを確認し、必要なら副問い合わせで事前にDISTINCTを行う方法を検討してください。

古いOracleで重複を除外する場合の考え方

LISTAGG内でDISTINCTを利用できない環境では、先に副問い合わせで重複行を除外し、その結果へLISTAGGを適用する方法があります。

SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employee_list FROM (SELECT DISTINCT department_id, employee_name FROM employee) GROUP BY department_id;

この方法では、内側のSELECT DISTINCTでdepartment_idとemployee_nameの重複した組み合わせを取り除き、その後LISTAGGで連結します。

古いシステムを保守している場合には「現在のOracleで使える構文」をそのまま適用するのではなく、対象データベースのバージョンを確認することが大切です。

LISTAGGの結果が長すぎる場合には注意

LISTAGGで大量の文字列を連結すると、戻り値として許容される長さを超えることがあります。Oracleの設定やバージョンなどによって上限に関する条件があるため、何千件もの長い文字列をまとめる処理では特に注意が必要です。

OracleのLISTAGGには、連結結果が戻り値の上限を超えた場合の処理を指定するON OVERFLOW句があります。

何も対策せず上限を超えた場合には、デフォルトのON OVERFLOW ERRORによってORA-01489エラーとなります。一方、対応バージョンではON OVERFLOW TRUNCATEを指定し、収まりきらない部分を切り詰めることができます。

LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_id) ON OVERFLOW TRUNCATE

大量データを扱うシステムでは、「通常は数件だから大丈夫」と決めつけず、最大で何件連結される可能性があるのかを設計時に確認しておくと安全です。

ON OVERFLOW TRUNCATEで省略された件数を表示する

ON OVERFLOW TRUNCATEには、切り詰めが発生したことを示す文字列や、切り捨てられた値の数を付加するための指定もあります。

例えば次のような書き方があります。

LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_id) ON OVERFLOW TRUNCATE '...' WITH COUNT

これにより、結果が長すぎた場合に末尾を省略し、切り捨てられた値の件数を示すことができます。なお、ON OVERFLOW句の細かな仕様や利用可能なバージョンについては、実際に使用しているOracle Databaseの公式リファレンスで確認するのが確実です。

グループ化せず全行を1つに連結することもできる

LISTAGGは必ずGROUP BYとセットで使わなければならないわけではありません。対象となる全行を1つの文字列にまとめたい場合には、GROUP BYを使用しない集計として利用できます。

SELECT LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_id) AS all_employees FROM employee;

この場合、テーブル全体が1つの集合として扱われ、すべての社員名が1行に連結されます。

つまり、「部署ごと」「顧客ごと」ならGROUP BYを使用し、「全データを1つにまとめる」ならGROUP BYを付けないという使い分けができます。

複数列の情報を組み合わせてLISTAGGすることも可能

連結する値は、単純な1列だけでなく式にすることもできます。例えば社員番号と社員名を「1001:田中」のようにまとめてから連結することもできます。

SELECT department_id, LISTAGG(employee_id || ':' || employee_name, ', ') WITHIN GROUP (ORDER BY employee_id) AS employee_list FROM employee GROUP BY department_id;

結果は「1001:田中, 1002:佐藤, 1003:鈴木」のようになります。

レポート用SQLや確認用SQLでは便利な方法ですが、アプリケーション側で後から値を分解することを前提にする場合は注意が必要です。データそのものに「:」や「,」が含まれていると解析が難しくなるため、構造化データとして扱うべき情報を無理にLISTAGGで文字列化しないことも重要です。

分析関数としてのLISTAGGもある

LISTAGGには、GROUP BYで行数そのものをまとめる集計用途だけでなく、分析関数として使用する形もあります。

分析関数として利用すると、PARTITION BYでグループを定義しながら、元の行を維持した状態でグループ内の連結結果を取得できます。

例えば「各社員の行は残したまま、その社員が所属する部署の社員一覧も表示する」といった処理に利用できます。単純に1グループ1行を作りたいだけなら通常の集計形式のLISTAGGの方が理解しやすいため、目的によって使い分けましょう。

LISTAGGでよくある間違い

LISTAGGを初めて使う場合には、いくつかつまずきやすいポイントがあります。代表的なものを整理すると次の通りです。

• GROUP BYする列を間違え、想定より細かくグループが分かれる
• WITHIN GROUPのORDER BYを適切に指定せず、期待する順番にならない
• 重複データを考慮せず、同じ文字列が何度も表示される
• NULLが自動的に文字として表示されると思ってしまう
• 大量データを連結し、戻り値の上限を超える
• 新しいOracleで使える構文を古いOracleへそのまま持ち込む

特に「どの単位で1行にするのか」と「どの順番で値を並べるのか」は、SQLを書く前に明確にしておくとLISTAGGを扱いやすくなります。

実務では「グループ・値・区切り・順番」の4点を決める

LISTAGGを使うときは、まず4つの項目を整理するとSQLを組み立てやすくなります。

1. グループ：部署別、顧客別、注文別など、何を1行にするか
2. 値：社員名、商品名、カテゴリなど、何を連結するか
3. 区切り：カンマ、スラッシュ、スペースなど、何で区切るか
4. 順番：ID順、日付順、名称順など、どう並べるか

例えば「顧客ごとに購入した商品名を購入日時順に『 / 』区切りで表示したい」のであれば、顧客IDをGROUP BYし、商品名をLISTAGGの対象にして、「 / 」を区切り文字、購入日時をWITHIN GROUPのORDER BYに指定する、という形で考えられます。

このように要件を分解すると、複雑に見えるLISTAGGも比較的簡単に組み立てられます。

まとめ｜LISTAGGならグループ化された複数行を区切り文字付きで1行にできる

Oracle SQLのLISTAGG関数を利用すると、複数行に分かれている文字列を、指定した区切り文字で連結して1行として取得できます。GROUP BYと組み合わせれば、部署別・顧客別・注文別などのグループ単位で一覧文字列を作成できます。

基本形はLISTAGG(連結対象, ‘区切り文字’) WITHIN GROUP (ORDER BY 並び順)です。例えば「LISTAGG(employee_name, ‘, ‘) WITHIN GROUP (ORDER BY employee_id)」とすれば、社員名を社員番号順のカンマ区切り文字列として取得できます。

実務では、並び順、NULL、重複値、連結結果の長さにも注意が必要です。対応するOracle DatabaseではDISTINCTによる重複除外やON OVERFLOW TRUNCATEによる長すぎる結果への対処も利用できますが、利用可能な構文はバージョンによって異なるため、運用環境のOracle公式ドキュメントを確認してください。

「複数行を1つの表示用文字列にまとめたい」という要件では非常に便利な関数なので、まずはグループ・連結する値・区切り文字・並び順の4点を決めてからSQLを作成すると、意図した結果を得やすくなります。