PostgreSQL string_aggの使い方｜並び順とDISTINCT

string_agg とは、複数行の文字列を区切り文字でつないで1つの文字列にまとめる PostgreSQL の集約関数です。 string_agg(列, 区切り文字) と書き、GROUP BY と組み合わせれば「チームごとのメンバー名をカンマ区切りで1行に」といった結果が1つの SQL で得られます。

この記事では、サンプルテーブルの CREATE TABLE と INSERT から載せ、PostgreSQL 16.15 で実際に実行した結果をそのまま並べます。並び順の指定（ORDER BY）、重複の除去（DISTINCT）、条件付き集約（FILTER）、ウィンドウ関数、NULL の扱い、数値列のキャスト、そして DISTINCT と別の列での ORDER BY を併用したときのエラー文と回避策まで、コピーして試せる形で整理しました。

string_agg の早見表

ORDER BY は関数のかっこの中、区切り文字のあとに書くのが string_agg の特徴です。

検証した環境とサンプルテーブル

次の SQL をそのまま流すと、この記事と同じテーブルができます。名前が NULL の行（id 5）、スキルが NULL の行（id 7）、点数が NULL の行（id 5）をわざと混ぜています。

CREATE TABLE members (
  id     integer PRIMARY KEY,
  team   text    NOT NULL,
  name   text,
  skill  text,
  score  integer,
  joined date    NOT NULL
);

INSERT INTO members (id, team, name, skill, score, joined) VALUES
  (1, 'A', '佐藤', 'SQL',    80,   '2024-04-01'),
  (2, 'A', '鈴木', 'Java',   65,   '2023-10-01'),
  (3, 'A', '高橋', 'SQL',    90,   '2025-04-01'),
  (4, 'B', '田中', 'Python', 70,   '2024-10-01'),
  (5, 'B', NULL,   'Java',   NULL, '2025-01-15'),
  (6, 'B', '伊藤', 'Python', 85,   '2022-04-01'),
  (7, 'C', '渡辺', NULL,     75,   '2025-07-01');

基本：複数行を1つの文字列にまとめる

SELECT string_agg(name, ',') AS names FROM members;

             names
-------------------------------
 佐藤,鈴木,高橋,田中,伊藤,渡辺

7行のうち、名前が NULL の id 5 は結果に含まれず、6人分がつながりました。構文は次の形です。

string_agg(つなぐ値, 区切り文字 [ORDER BY 並び替えの式])

引数は2つとも必須です。区切り文字を省くと次のエラーになります。

SELECT string_agg(name) FROM members;

ERROR:  function string_agg(text) does not exist
LINE 1: SELECT string_agg(name) FROM members;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

区切りなしでつなぎたいときは、空文字 '' を渡します。

区切り文字を変える（スラッシュ・区切りなし・改行）

SELECT string_agg(name, ' / ')  AS slash,
       string_agg(name, '')     AS no_sep,
       string_agg(name, E'\n')  AS newline
FROM members
WHERE team = 'A';

       slash        |    no_sep    | newline
--------------------+--------------+---------
 佐藤 / 鈴木 / 高橋 | 佐藤鈴木高橋 | 佐藤   +
                    |              | 鈴木   +
                    |              | 高橋

改行やタブは、先頭に E を付けたエスケープ文字列で書きます。psql の表示では、改行を含む値の行末に + が付きます。なお、区切り文字に NULL を渡した場合は区切りなしでつながりました（string_agg(name, NULL) の結果は 佐藤鈴木高橋）。公式ドキュメントの「区切り文字が NULL でなければ前に付ける」という説明どおりの動きです。

ORDER BY で並び順を指定する

ORDER BY を書かない場合、つながる順番は決まっていません。 公式ドキュメントにも「順序は既定では不定で、集約関数の呼び出しの中に ORDER BY を書くことで制御できる」とあります。順番に意味があるときは必ず指定します。

SELECT string_agg(name, ',' ORDER BY joined)                AS by_joined,
       string_agg(name, ',' ORDER BY score DESC NULLS LAST) AS by_score_desc
FROM members;

           by_joined           |         by_score_desc
-------------------------------+-------------------------------
 伊藤,鈴木,佐藤,田中,高橋,渡辺 | 高橋,伊藤,佐藤,渡辺,田中,鈴木

左は入社日の早い順、右は点数の高い順です。つなぐ列（name）と並び替えの列（joined や score）は別でかまいません。

ORDER BY を書く位置

ORDER BY をかっこの外に書くと、構文エラーになります。

SELECT string_agg(name, ',') ORDER BY id FROM members;

ERROR:  syntax error at or near "FROM"
LINE 1: SELECT string_agg(name, ',') ORDER BY id FROM members;
                                                 ^

連結の順番は関数の中の ORDER BY、結果の行の順番は文末の ORDER BY、と役割が分かれています。

並び替えたサブクエリを FROM に置く書き方も、公式ドキュメントに「たいていはうまく動く」と書かれており、手元でも並びは保たれました。ただ、関数の中に ORDER BY を書く形のほうが意図が1行で伝わるので、この記事ではそちらを基本にしています。

GROUP BY でグループごとにまとめる

いちばんよく使う形です。チームごとに、人数とメンバー名を1行にまとめます。

SELECT team,
       count(*)                          AS cnt,
       string_agg(name, ',' ORDER BY id) AS names
FROM members
GROUP BY team
ORDER BY team;

 team | cnt |     names
------+-----+----------------
 A    |   3 | 佐藤,鈴木,高橋
 B    |   3 | 田中,伊藤
 C    |   1 | 渡辺

チーム B は count(*) が 3 ですが、名前は2人分です。名前が NULL の行が string_agg では読み飛ばされるためで、件数と連結結果の個数が合わないときは NULL を確認します（対応は後述します）。

GROUP BY を付け忘れると、次のエラーになります。

SELECT team, string_agg(name, ',') FROM members;

ERROR:  column "members.team" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT team, string_agg(name, ',') FROM members;
               ^

集約関数と一緒に SELECT に書いた列は、GROUP BY にも書く、という決まりです。

DISTINCT で重複を除く

スキルの一覧のように、同じ値が何度も出る列をまとめるときに使います。

SELECT string_agg(skill, ',' ORDER BY skill) AS all_skills,
       string_agg(DISTINCT skill, ',')       AS distinct_skills
FROM members;

           all_skills            | distinct_skills
---------------------------------+-----------------
 Java,Java,Python,Python,SQL,SQL | Java,Python,SQL

DISTINCT と ORDER BY を一緒に書くこともできます。並び替えの式が、つなぐ値そのものであれば問題なく動きます。

SELECT team,
       string_agg(DISTINCT skill, ',' ORDER BY skill DESC) AS skills
FROM members
GROUP BY team
ORDER BY team;

 team |   skills
------+-------------
 A    | SQL,Java
 B    | Python,Java
 C    |

チーム C はスキルが NULL の1行だけなので、結果は NULL（空欄）です。

DISTINCT と別の列での ORDER BY を併用するとエラーになる

「重複を除きつつ、入社日の早い順に並べたい」と考えて次のように書くと、エラーになります。

SELECT string_agg(DISTINCT skill, ',' ORDER BY joined) FROM members;

ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: SELECT string_agg(DISTINCT skill, ',' ORDER BY joined) FROM ...
                                                       ^

DISTINCT を付けた集約では、ORDER BY に書けるのは引数に出てくる式だけ、という決まりです。理由は、重複を1つにまとめたあとでは「どの行の joined で並べるか」が決まらないからです。たとえば SQL は 2024-04-01 の行と 2025-04-01 の行の両方にあり、どちらを使うかを SQL 側で指定する必要があります。

回避策1：サブクエリで先に重複をまとめる

「スキルごとの最も早い入社日」を先に求め、その日付で並べます。

SELECT string_agg(skill, ',' ORDER BY first_joined) AS skills_by_first_joined
FROM (
  SELECT skill, min(joined) AS first_joined
  FROM members
  WHERE skill IS NOT NULL
  GROUP BY skill
) AS s;

 skills_by_first_joined
------------------------
 Python,Java,SQL

GROUP BY で重複をなくし、並び替えに使う値を min や max で1つに決めるのがポイントです。「最も遅い日付で並べたい」なら max に変えるだけです。

回避策2：DISTINCT ON と array_agg を使う

PostgreSQL の DISTINCT ON で「スキルごとに先頭の1行」を選び、array_agg で並べてから文字列にする方法です。

SELECT array_to_string(array_agg(skill ORDER BY first_joined), ',') AS skills
FROM (
  SELECT DISTINCT ON (skill) skill, joined AS first_joined
  FROM members
  WHERE skill IS NOT NULL
  ORDER BY skill, joined
) AS s;

     skills
-----------------
 Python,Java,SQL

FILTER で条件に合う行だけを集約する

1つの SELECT の中で、条件の違う連結結果を横に並べたいときは FILTER が便利です。

SELECT team,
       string_agg(name, ',' ORDER BY id) FILTER (WHERE score >= 80) AS high,
       string_agg(name, ',' ORDER BY id) FILTER (WHERE score <  80) AS others
FROM members
GROUP BY team
ORDER BY team;

 team |   high    | others
------+-----------+--------
 A    | 佐藤,高橋 | 鈴木
 B    | 伊藤      | 田中
 C    |           | 渡辺

チーム C の high が空欄（NULL）なのは、80点以上の人がいないためです。空文字にしたいときは COALESCE(..., '') で包みます。

NULL の扱いと COALESCE

string_agg は NULL の値を読み飛ばします。 公式ドキュメントの説明も「NULL でない入力値を連結する」です。挙動を3つの場面で確かめました。

値に NULL が混ざっているとき

SELECT team,
       string_agg(name, ',' ORDER BY id)                       AS skip_null,
       string_agg(COALESCE(name, '(未登録)'), ',' ORDER BY id) AS with_coalesce
FROM members
GROUP BY team
ORDER BY team;

 team |   skip_null    |   with_coalesce
------+----------------+--------------------
 A    | 佐藤,鈴木,高橋 | 佐藤,鈴木,高橋
 B    | 田中,伊藤      | 田中,(未登録),伊藤
 C    | 渡辺           | 渡辺

読み飛ばされるので、田中,,伊藤 のように区切りが2つ続くことはありません。NULL の行も位置を残したいときは、COALESCE で文字に置き換えてから渡します。

対象が0行、または全部 NULL のとき

SELECT string_agg(name, ',')               AS no_rows,
       string_agg(name, ',') IS NULL       AS is_null,
       COALESCE(string_agg(name, ','), '') AS empty
FROM members
WHERE team = 'Z';

 no_rows | is_null | empty
---------+---------+-------
         | t       |

対象が0行のとき、string_agg は空文字ではなく NULL を返します。 名前が NULL の行だけを集めた場合も、同じく NULL になりました。アプリケーション側で文字列として扱うなら、COALESCE(string_agg(...), '') と外側を包んでおくと安心です。

複数の列を組み合わせてつなぐときの NULL

「名前(点数)」の形でつなぐとき、連結の方法で NULL の行の扱いが変わります。

SELECT string_agg(name || '(' || score || ')', ', ' ORDER BY score DESC)      AS concat_op,
       string_agg(format('%s(%s)', name, score), ', ' ORDER BY score DESC)   AS with_format
FROM members;

                         concat_op                          |                          with_format
------------------------------------------------------------+----------------------------------------------------------------
 高橋(90), 伊藤(85), 佐藤(80), 渡辺(75), 田中(70), 鈴木(65) | (), 高橋(90), 伊藤(85), 佐藤(80), 渡辺(75), 田中(70), 鈴木(65)

どちらが正しいということはなく、NULL の行を出したいかどうかで選びます。 先頭に () が来ているのは、ORDER BY score DESC の既定では NULL が先頭に並ぶためで、NULLS LAST を付けると末尾に移ります。

数値や日付の列をつなぐ（キャスト）

string_agg の第1引数は文字列型です。integer の列をそのまま渡すとエラーになります。

SELECT string_agg(score, ',') FROM members;

ERROR:  function string_agg(integer, unknown) does not exist
LINE 1: SELECT string_agg(score, ',') FROM members;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

::text を付けて文字列に変換すると通ります。日付は to_char で書式を決めると、表示が安定します。

SELECT string_agg(score::text, ',' ORDER BY score)                 AS scores,
       string_agg(to_char(joined, 'YYYY/MM'), ',' ORDER BY joined) AS months
FROM members;

      scores       |                         months
-------------------+---------------------------------------------------------
 65,70,75,80,85,90 | 2022/04,2023/10,2024/04,2024/10,2025/01,2025/04,2025/07

ORDER BY には元の列（score や joined）を使っている点がポイントです。文字列に変換した値で並べると「100」が「65」より前に来るような辞書順になるため、並び替えは元の型の列で行います。

ウィンドウ関数として使う（行を残したまま連結）

GROUP BY は行をまとめますが、OVER を付けると、元の行を残したまま各行に連結結果を付けられます。

SELECT id, team, name,
       string_agg(name, ',') OVER (PARTITION BY team)             AS team_all,
       string_agg(name, ',') OVER (PARTITION BY team ORDER BY id) AS running
FROM members
ORDER BY id;

 id | team | name |    team_all    |    running
----+------+------+----------------+----------------
  1 | A    | 佐藤 | 佐藤,鈴木,高橋 | 佐藤
  2 | A    | 鈴木 | 佐藤,鈴木,高橋 | 佐藤,鈴木
  3 | A    | 高橋 | 佐藤,鈴木,高橋 | 佐藤,鈴木,高橋
  4 | B    | 田中 | 田中,伊藤      | 田中
  5 | B    |      | 田中,伊藤      | 田中
  6 | B    | 伊藤 | 田中,伊藤      | 田中,伊藤
  7 | C    | 渡辺 | 渡辺           | 渡辺

ウィンドウ関数として使うときは、並び順を OVER の中の ORDER BY で指定します。 関数のかっこの中に ORDER BY を書くと、次のエラーになります。

SELECT string_agg(name, ',' ORDER BY id) OVER (PARTITION BY team) FROM members;

ERROR:  aggregate ORDER BY is not implemented for window functions
LINE 1: SELECT string_agg(name, ',' ORDER BY id) OVER (PARTITION BY ...
               ^

応用：グループごとに上位 N 件だけをつなぐ

string_agg 自体には件数を絞る指定がないため、row_number で順位を付けてから絞り込みます。

SELECT team, string_agg(name, ',' ORDER BY score DESC) AS top2
FROM (
  SELECT team, name, score,
         row_number() OVER (PARTITION BY team ORDER BY score DESC NULLS LAST) AS rn
  FROM members
) AS r
WHERE rn <= 2
GROUP BY team
ORDER BY team;

 team |   top2
------+-----------
 A    | 高橋,佐藤
 B    | 伊藤,田中
 C    | 渡辺

つないだ文字列を行に戻したいときは、string_to_array と unnest を組み合わせます。

SELECT unnest(string_to_array('SQL,Java,Python', ',')) AS skill;

 skill
--------
 SQL
 Java
 Python

長さについても確かめました。1 から 100000 までの数字をカンマでつないだ結果は 588,894 文字になり、途中で切られることなく全体が返りました。

SELECT length(string_agg(g::text, ',')) AS len FROM generate_series(1, 100000) AS g;

  len
--------
 588894

array_agg・array_to_string・json_agg との使い分け

「複数行を1つにまとめる」関数は string_agg のほかにもあります。同じデータで結果を並べました。

SELECT team,
       array_agg(name ORDER BY id)                            AS arr,
       array_to_string(array_agg(name ORDER BY id), ',')      AS arr_str,
       array_to_string(array_agg(name ORDER BY id), ',', '-') AS arr_str_null,
       json_agg(name ORDER BY id)                             AS js
FROM members
GROUP BY team
ORDER BY team;

 team |       arr        |    arr_str     |  arr_str_null  |            js
------+------------------+----------------+----------------+--------------------------
 A    | {佐藤,鈴木,高橋} | 佐藤,鈴木,高橋 | 佐藤,鈴木,高橋 | ["佐藤", "鈴木", "高橋"]
 B    | {田中,NULL,伊藤} | 田中,伊藤      | 田中,-,伊藤    | ["田中", null, "伊藤"]
 C    | {渡辺}           | 渡辺           | 渡辺           | ["渡辺"]

選び方の目安は次のとおりです。

配列なら添字で取り出せます。点数の最も高い人を取り出す例と、JSON オブジェクトの配列を作る例です。

SELECT (array_agg(name ORDER BY score DESC NULLS LAST))[1] AS top1,
       cardinality(array_agg(name))                        AS n
FROM members;

SELECT json_agg(json_build_object('name', name, 'score', score) ORDER BY id) AS objs
FROM members
WHERE team = 'A';

 top1 | n
------+---
 高橋 | 7

                                                objs
-----------------------------------------------------------------------------------------------------
 [{"name" : "佐藤", "score" : 80}, {"name" : "鈴木", "score" : 65}, {"name" : "高橋", "score" : 90}]

array_agg は NULL も数えるため、n は 7 になっています。値にカンマが含まれる可能性があるデータは、文字列でつなぐより配列や JSON で受け取るほうが、あとで分解するときに迷いません。

他のデータベースとの対応表

同じ目的の関数は、データベースごとに名前と書き方が違います。移行や読み替えのときの対応表です。

このうち MySQL は、8.0.46 で同じデータを入れて実行しました。

-- MySQL 8.0.46
SELECT team,
       GROUP_CONCAT(name ORDER BY id SEPARATOR ',')               AS names,
       GROUP_CONCAT(DISTINCT skill ORDER BY skill SEPARATOR '/')  AS skills
FROM m
GROUP BY team;

+------+----------------+-------------+
| team | names          | skills      |
+------+----------------+-------------+
| A    | 佐藤,鈴木,高橋 | Java/SQL    |
| B    | 田中,伊藤      | Java/Python |
+------+----------------+-------------+

Oracle Database の LISTAGG と SQL Server の STRING_AGG は今回は未実行で、表の書き方は一般に知られた構文です。細かな挙動（DISTINCT の可否、長さの上限、NULL の扱い）は、使うバージョンのドキュメントで確かめてください。

つまずきやすいエラーと直し方

この記事で再現したエラーを、原因と直し方でまとめます。

エラーにはならないけれど結果が想定と違う、というときのチェックリストです。

よくある質問

Q. string_agg の結果の順番は、何も指定しないとどうなりますか？ A. 決まっていません。小さなテーブルでは登録順に見えることが多いですが、公式ドキュメントでは既定の順序は不定とされています。順番に意味があるときは string_agg(name, ',' ORDER BY id) のように関数の中で指定します。

Q. DISTINCT と ORDER BY は一緒に使えますか？ A. 使えます。ただし ORDER BY に書けるのは、つなぐ値と同じ式だけです。string_agg(DISTINCT skill, ',' ORDER BY skill) は動き、別の列で並べようとするとエラーになります。別の列で並べたいときは、サブクエリで先に GROUP BY して min や max で並び替えの値を決めます。

Q. 該当する行がないとき、空文字にするにはどうすればよいですか？ A. COALESCE(string_agg(name, ','), '') と書きます。string_agg は対象が0行のとき NULL を返すためです。

Q. string_agg と array_agg はどちらを使えばよいですか？ A. 人が読む文字列が欲しいなら string_agg、プログラムで要素ごとに処理するなら array_agg が向いています。array_agg は NULL も要素として残る点が違います。

まとめ

• string_agg(値, 区切り文字) は複数行を1つの文字列にまとめる集約関数。引数は2つとも必須
• 並び順は関数のかっこの中に ORDER BY を書いて指定する。書かない場合の順番は決まっていない
• DISTINCT を付けたときの ORDER BY は、つなぐ値と同じ式だけ。別の列で並べたいときはサブクエリで重複を先にまとめる
• NULL の値は読み飛ばされ、対象が0行なら結果は NULL。値は COALESCE で置き換え、結果は COALESCE で包む
• 数値や日付は ::text や to_char で文字列にしてから渡し、並び替えは元の列で行う
• FILTER で条件別の連結を横に並べ、OVER で行を残したまま連結できる。OVER を使うときの並び順は OVER の中に書く
• 配列で受け取るなら array_agg、JSON なら json_agg。値に区切り文字が含まれるデータは配列や JSON が扱いやすい

参考・出典

• PostgreSQL 公式ドキュメント「9.21. Aggregate Functions」（string_agg・array_agg・json_agg の説明、入力順序と0行のときの戻り値の注記を確認）https://www.postgresql.org/docs/current/functions-aggregate.html

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。