Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

グループ内の結果セットを単一列に連結する

LISTAGG ( expr [, delimiter] ) WITHIN GROUP ( order_by )
return [ RAW | VARCHAR2 ]

expr	数値式、バイナリ、または、文字列
delimiter	接続文字列	default ''
order_by	グループセット内の連結順

• expr の結果グループセットを delimiter で連結した文字列、または、バイナリ文字列を戻す。

• SQL でのみ使用可能。(PL/SQL の埋め込み SQL 含む）

⇒ Oracle 10g で LISTAGG を実装する

LISTAGG 集計関数の内容

グループにおける 数値式、文字列式、または、バイナリ文字列式 expr の連結 結果を戻す。
言い換えると、縦並びの表示結果を横に並び替えた上、カンマ区切りなどで１つの文字列に変換することができる。
expr が数値、文字列、日付の場合には文字列に暗黙変換され文字列として戻される。 RAW 型の場合には RAW 型が戻される。

MySQL における group_concat 関数の Oracle 版である。

LISTAGG 使用例

全員の name を並び順を気にせずに連結する。
WITHIN GROUP は省略できないので意味のない 「order by null」 を指定してみた。数値を指定してもカラムの第 N 番目でソートというわけでない。
Oracle 11g R2 時点では実行計画に SORT GROUP BY があり、暗黙的に expr 昇順にソートされている。

SQL> select LISTAGG(name) WITHIN GROUP (order by null) concat_name
  2    from listagg_sample;
 
CONCAT_NAME
--------------------------------------------------
あおきこばやしさとうすずきたかはしやまもと

部門毎に名前をカンマ区切りで連結する。名前の並び順は給料の高い順

SQL> select dept, LISTAGG(name, ',') WITHIN GROUP (order by saraly desc nulls last) csv_name
  2   from listagg_sample
  3  group by dept;
 
DEPT     CSV_NAME
-------- -------------------------------
開発部   こばやし,さとう,やまもと,あおき
管理部   たかはし,すずき

他の集計関数同様 NULL の項目は除外されるため他の項目とのマッチングはできない。

SQL> select
  2      LISTAGG(name,',') WITHIN GROUP (order by name) csv_name,
  3      LISTAGG(saraly,',') WITHIN GROUP (order by name) csv_saraly
  4   from listagg_sample;
 
CSV_NAME                                           CSV_SARALY
-------------------------------------------------- ----------------------------------
あおき,こばやし,さとう,すずき,たかはし,やまもと    500000,500000,300000,400000,200000

saraly の リスト途中の NULL を CSV のリストに加える書き方の一例（末尾の連続した NULL は省略する）

SQL> select
  2     LISTAGG(name,',') WITHIN GROUP (order by name) csv_name,
  3     rtrim(
  4         LISTAGG(saraly||',') WITHIN GROUP (order by name)
  5        , ','
  6     )  csv_saraly
  7  from listagg_sample;
 
CSV_NAME                                           CSV_SARALY
-------------------------------------------------- -----------------------------------
あおき,こばやし,さとう,すずき,たかはし,やまもと    ,500000,500000,300000,400000,200000

---

LISTAGG 関連

• 文字列からトークンを切り出す
• Oracle 10g で ListAgg を実現する