Oracle 複数行を連結してまとめる 集約関数「LISTAGG」

複数行を連結してまとめる 集約関数「LISTAGG」

複数行を連結して1行にすることができます。
しかもデリミタを指定することができるのでカンマ区切り等にして出力することも容易です。

集約関数「LISTAGG」の基本構文

LISTAGG ( expr [, delimiter] ) WITHIN GROUP ( order_by_clause )

検証用のテーブルとテストデータ

SQL> desc animal1;
 名前                  NULL?    型
 --------------------- -------- ---------------
 ID                             VARCHAR2(10)
 NAME                           VARCHAR2(20)
 ADDRESS                        VARCHAR2(20)

SQL> select * from animal1;

ID    NAME       ADDRESS
----- ---------- ----------
001   いるか     海
002   うさぎ     陸
003   ぺんぎん   空
004   あざらし   海
005   ふくろう   空

集約関数「LISTAGG」の実行例

複数行の「NAME」を「id」の昇順で連結した例

SELECT
    LISTAGG(name) WITHIN GROUP (ORDER BY id)
FROM
    animal1;

SQL> SELECT
  2      LISTAGG(name) WITHIN GROUP (ORDER BY id)
  3  FROM
  4      animal1;

LISTAGG(NAME)WITHINGROUP(ORDERBYID)
--------------------------------------------------
いるかうさぎぺんぎんあざらしふくろう

複数行の「NAME」を「id」の降順で連結した例

SELECT
    LISTAGG(name) WITHIN GROUP (ORDER BY id desc)
FROM
    animal1;

SQL> SELECT
  2      LISTAGG(name) WITHIN GROUP (ORDER BY id desc)
  3  FROM
  4      animal1;

LISTAGG(NAME)WITHINGROUP(ORDERBYIDDESC)
------------------------------------------------------
ふくろうあざらしぺんぎんうさぎいるか

複数行の「NAME」を「id」の昇順でデリミタをカンマ「,」で連結した例

SELECT
    LISTAGG(name,',') WITHIN GROUP (ORDER BY id)
FROM
    animal1;

SQL> SELECT
  2      LISTAGG(name,',') WITHIN GROUP (ORDER BY id)
  3  FROM
  4      animal1;

LISTAGG(NAME,',')WITHINGROUP(ORDERBYID)
------------------------------------------------------
いるか,うさぎ,ぺんぎん,あざらし,ふくろう

「ORDER BY null」の例

基本構文を確認してみてほしいのだが、「order by」を省略することはできません。
でも別に並び順を指定しなくてもいいという場合は、「ORDER BY null」って記述することができます。
ただし、後述するけど勝手に暗黙のソートされるから何かしら指定することをおすすめします。

SELECT
    LISTAGG(name,',') WITHIN GROUP (ORDER BY null)
FROM
    animal1;

SQL> SELECT
  2      LISTAGG(name) WITHIN GROUP (ORDER BY null)
  3  FROM
  4      animal1;

LISTAGG(NAME)WITHINGROUP(ORDERBYNULL)
----------------------------------------------------
あざらしいるかうさぎふくろうぺんぎん

実行計画を確認する限り連結する指定列の昇順で勝手に暗黙のソートが行われてるみたいです。
使用しているバージョンによっては変わるかも。。。

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

SELECT
    address
    ,LISTAGG(name) WITHIN GROUP (ORDER BY id)
FROM
    animal1
GROUP BY
    address;

SQL> SELECT
  2      address
  3      ,LISTAGG(name) WITHIN GROUP (ORDER BY id)
  4  FROM
  5      animal1
  6  GROUP BY
  7      address;

ADDRESS    LISTAGG(NAME)WITHINGROUP(ORDERBYID)
---------- ---------------------------------------
海         いるかあざらし
空         ぺんぎんふくろう
陸         うさぎ