(Oracle) LISTAGGで複数行を1行に集約する

本記事では、
Oracleで複数行を連結して1行に集約することができる「LISTAGG」関数について解説していく。

LISTAGG –複数行を連結して1行に集約する–

LISTAGGを使えば、
複数行を連結して1行に集約することが可能だ。

その際、
デリミタを指定して集約することができるのでカンマ区切り等にして出力することもできる。

--LISTAGGのサンプル
LISTAGG(動物, ',') WITHIN GROUP (ORDER BY ID)

→ かめ,いぬ,うし,たぬき --動物列を1行に集約（,区切り）

+------+----+
| 動物 | ID |
+------+----+
|  かめ | 1 |
|  いぬ | 2 |
|  うし | 3 |
|  たぬき | 4 |
+------+----+

LISTAGGの使い方

LISTAGGの使い方は以下のようになっている。

LISTAGG ( 連結列 [, デリミタ] ) WITHIN GROUP ( order by 連結順 )

連結列を、
指定のデリミタで区切りながら、
連結順で連結する

という流れになっている。

LISTAGGを使った集約の実行例

では以下に
LISTAGGを使った集約のサンプルを、パターン毎にいくつか紹介していく。

テーブルは以下の様なテストデータで行うものとする。

--テーブル1
+------+----+-------+
|  動物 | ID | 生息地 |
+------+----+-------+
|     かめ | 1 | 海 |
|     いぬ | 2 | 陸 |
|     うし | 3 | 陸 |
|   たぬき | 4 | 陸 |
|  ペリカン | 5 | 空 |
|  はやぶさ | 6 | 空 |
+------+----+-------+

① 複数行の「動物」列を、「ID」の昇順で集約した例

以下は、複数行の「動物」列を、「ID」の昇順で集約した例である。

SELECT
  LISTAGG(動物) WITHIN GROUP (ORDER BY ID)
FROM
  テーブル1;

--実行結果
かめいぬうしたぬきペリカンはやぶさ

上記は最も基本的なLISTAGGの使い方だ。

デリミタ等は使用せず、ただ単純に「動物」カラムをIDの昇順で1行に集約した結果だ。

まずはこの使い方が基本となるのでしっかりと覚えておこう。

② 複数行の「動物」列を、「ID」の降順で集約した例

以下は、複数行の「動物」列を「ID」の降順で集約した例である。

SELECT
  LISTAGG(動物) WITHIN GROUP (ORDER BY ID desc)
FROM
  テーブル1;


--実行結果
はやぶさペリカンたぬきうしいぬかめ

上記は、
デリミタ等は使用せず、ただ単純に「動物」カラムをIDの降順で1行に集約した結果だ。

①の結果と比べて、降順にしただけなので特に使い方で疑問点は無いだろう。

③ 複数行の「動物」列を「ID」の昇順でデリミタをカンマで連結した例

以下は、
複数行の「動物」列を「ID」の昇順で、デリミタをカンマ「,」で集約した例である。

SELECT
  LISTAGG(動物,',') WITHIN GROUP (ORDER BY ID)
FROM
  テーブル1;


--実行結果
かめ,いぬ,うし,たぬき,ペリカン,はやぶさ

上記は①の例に比べて、デリミタをカンマ「,」で集約するよう条件を加えた例である。

列を1行に集約する際、デリミタに指定したカンマ「,」で値が区切られているのが分かるだろう。

④「GROUP BY」を使用した場合の例

以下は、
複数行の「動物」列を「ID」の昇順で、生息地をGROUP BYした集約の結果の例である。

SELECT
  生息地,
  LISTAGG(動物) WITHIN GROUP (ORDER BY ID)
FROM
  テーブル1
GROUP BY
  生息地;


--実行結果
+------++-------------+
| 生息地 | 動物         |
+------++-------------+
| 海 |　かめ           |
| 陸 |　いぬうしたぬき   |
| 空 |　ペリカンはやぶさ |
+------++-------------+

上記はLISTAGGを使って動物列を1行にまとめる際、
さらに生息地毎にGROUP BYする条件を加えたことによって、集約を1行ではなく、生息地のグループ毎に行の集約を細分化した例である。

実務的な話では、このGROUP BYと併用する実行の仕方がより実践的になると思うので、覚えておいてもらいと思う気持ちも込めて紹介した。