SQL Server で複数行の文字列をカンマ区切りに連結する（STRING_AGG と FOR XML PATH）

みなさんこんにちは！ヒロポンです！

SQL Server で SELECT した複数行を、山田, 佐藤, 鈴木 みたいにカンマ区切りの1文字列にまとめたい。帳票やログ出力だと、地味によく降ってくる要件ですよね。

ところがこれ、「行を横に連結する」だけなのに一発で書けない。俺も昔、部署ごとの担当者名を1セルに集約する帳票で、GROUP BY と睨めっこして30分溶かした。

結論から言います。SQL Server の文字列の連結・集約は、バージョンで書き方が2つに割れる。2017 以降なら STRING_AGG で一発。2016 以前なら FOR XML PATH + STUFF です。

この記事では、その両方をコピペで動く形で置いていきます。Docker の SQL Server で実際に動かして確認済み。GROUP BY でグループごとにまとめる書き方、それと特殊文字のエスケープや8000バイト制限っていう「知らないと一晩飛ぶ」やつまで、まとめて拾っていきます。

結論: バージョンで書き方が2つに割れる

やりたいことは1つ。なのに使える構文はバージョンで変わります。まず全体像から。

• SQL Server 2017 以降: STRING_AGG(列, ', ') を使う。これが本命。1行で終わる。
• SQL Server 2016 以前: STRING_AGG が存在しない。FOR XML PATH('') で行を連結し、STUFF で先頭の区切りを削る定番テクを使う。

現場が 2016 なのか 2017+ なのか。それで書ける手が決まる。最初にここを分岐させるのがポイントです。

自分の現場のバージョンが分からない時は、これで確認できます。

SELECT
    SERVERPROPERTY('ProductVersion')     AS version,
    SERVERPROPERTY('ProductMajorVersion') AS major;
-- major が 14 以上なら 2017+（STRING_AGG が使える）
-- 13 は 2016、12 は 2014、11 は 2012

major が 14 以上なら STRING_AGG が使える。13 以下なら FOR XML PATH ルート。そう覚えておけばいい感じに判断できます。

なぜバージョンで書き方が分かれるのか

STRING_AGG は SQL Server 2017 で追加された、まだあたらしめの集約関数です。「複数行を1文字列に連結する」って、他の DBMS だと当たり前にあった機能。それが SQL Server では長いあいだ標準に無かったんですよね。

じゃあ 2016 以前の現場はどうしてたか。FOR XML PATH('') の副作用を借りてたんです。本来は結果を XML に整形する機能なんですが、パス名を空文字にするとタグが消えて、値だけが順番に連結される。この挙動を「連結器」として流用するのが、長年の定番テクでした。

要するに FOR XML PATH の連結は、機能の正攻法じゃなくて 裏技が定着したもの。初見で「なんでこれで連結になるの？？」ってなるのはそのせいです。2017 で STRING_AGG が来て、ようやく素直に書けるようになった。

歴史的な事情なので、結局は 自分の現場のバージョンに合った方を選べばいい。両方書けるようにしておけば、2016 と 2019 が混在してる現場でも困りません。

最短対処: コピペで動く連結の書き方

ここから実際に動くコードです。まず検証用のテーブルを1つ。部署（dept_id）ごとにメンバーがぶら下がってる、業務系でよく見る形。

IF OBJECT_ID('dbo.Members') IS NOT NULL DROP TABLE dbo.Members;

CREATE TABLE dbo.Members (
    id      INT IDENTITY PRIMARY KEY,
    dept_id INT          NOT NULL,
    name    NVARCHAR(50) NOT NULL
);

INSERT INTO dbo.Members (dept_id, name) VALUES
    (1, N'山田'), (1, N'佐藤'), (1, N'鈴木'),
    (2, N'田中'), (2, N'高橋');

SELECT * FROM dbo.Members すると5行が縦に並ぶ。これを横1文字列に畳んでいきます。

STRING_AGG（2017 以降・これが本命）

全メンバーを1文字列に連結するだけなら、これで終わり！！

SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY id) AS names
FROM dbo.Members;

実行結果:

names
------------------------------
山田, 佐藤, 鈴木, 田中, 高橋

第1引数が連結したい列、第2引数が区切り文字（ここでは , ）。WITHIN GROUP (ORDER BY id) で並び順を指定しています。この WITHIN GROUP を省くと連結順が保証されない。だから順番に意味があるなら必ず付ける。ここ、あとでハマりポイントとしてもう一回出てきます。

FOR XML PATH + STUFF（2016 以前）

同じ結果を 2016 以前で出すと、こうなります。

SELECT STUFF(
    (SELECT ', ' + name
     FROM dbo.Members
     ORDER BY id
     FOR XML PATH('')), 1, 2, '') AS names;

実行結果:

names
------------------------------
山田, 佐藤, 鈴木, 田中, 高橋

読み解くと、こんな感じの二段構えです。

1. SELECT ', ' + name ... FOR XML PATH('') で , 山田, 佐藤, 鈴木, 田中, 高橋 という文字列を作る。各行の頭に , を付けて連結してる。
2. でも先頭に余計な , が残る。これを STUFF(文字列, 1, 2, '') で「1文字目から2文字分（, の2文字）を空文字に置換」して削る。

STUFF の 1, 2 は「開始位置1、削除長さ2」。区切りが , （カンマ＋スペースの2文字）だから 2 です。ここの桁がズレると先頭がおかしくなるので、区切り文字の長さと必ず合わせる。

GROUP BY でグループごとに連結する

実務でほしいのは「全部を1つ」より「部署ごとに1行」の方が多いですよね。部署ごとにメンバー名をまとめる場合。

2017+ なら GROUP BY に素直に乗ります。

SELECT dept_id,
       STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY id) AS member_names
FROM dbo.Members
GROUP BY dept_id
ORDER BY dept_id;

実行結果:

dept_id  member_names
-------  --------------------
1        山田, 佐藤, 鈴木
2        田中, 高橋

2016 以前だと、相関サブクエリで同じことをやる。処理は同じでも書き方の重さがけっこう違うんですよね。並べて見るとこう。

外側で部署の一覧（SELECT DISTINCT dept_id）を作る。内側のサブクエリで WHERE m.dept_id = d.dept_id と絞って、その部署ぶんだけ連結する。STRING_AGG に比べると、正直めんどくさい。でも 2016 現場だとこれが正解になります。

ハマりポイント: 知らないと一晩飛ぶやつ

ここからが本題です。連結自体は書けても、実データを流すと引っかかるポイントが4つある。全部、俺や周りが現場で実際に出くわしたやつです。

① FOR XML PATH は特殊文字がエスケープされる

これが一番やられる。& や < > を含むデータを FOR XML PATH で連結すると、XML の仕様でエスケープされて & < に化けます。

IF OBJECT_ID('dbo.Vendors') IS NOT NULL DROP TABLE dbo.Vendors;
CREATE TABLE dbo.Vendors (id INT IDENTITY PRIMARY KEY, name NVARCHAR(50) NOT NULL);
INSERT INTO dbo.Vendors (name) VALUES (N'A&B商事'), (N'C<D工業'), (N'鈴木製作所');

-- ❌ 素の FOR XML PATH だと化ける
SELECT STUFF(
    (SELECT ', ' + name
     FROM dbo.Vendors
     ORDER BY id
     FOR XML PATH('')), 1, 2, '') AS vendor_list;

実行結果:

vendor_list
-----------------------------------------
A&B商事, C<D工業, 鈴木製作所

取引先名が A&B商事 になってる。帳票にこれが出たら事故ですよね。対処は FOR XML PATH(''), TYPE で XML 型として受け取り、.value('.', 'NVARCHAR(MAX)') で取り出す。これでエスケープが元に戻ります。

-- ⭕ TYPE + .value でエスケープを戻す
SELECT STUFF(
    (SELECT ', ' + name
     FROM dbo.Vendors
     ORDER BY id
     FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS vendor_list;

実行結果:

vendor_list
-----------------------------------------
A&B商事, C<D工業, 鈴木製作所

A&B商事 に戻りました。FOR XML PATH で連結する時は、この TYPE + .value をほぼ定型でセットにしておくのが安全。ちなみに STRING_AGG はこの問題が最初から無いので、2017+ なら気にしなくていいです。

② STUFF の削除桁を区切り文字と合わせる

さっきも触れたやつ。STUFF(x, 1, 2, '') の 2 は「先頭の区切り文字の長さ」です。区切りを , （2文字）にしてるなら 2。

ここで区切りを ／（1文字）や /（3文字）に変えたのに、STUFF の桁を直し忘れると先頭の文字が欠けたり余ったりする。区切りを変えたら STUFF の第3引数も必ずセットで直す。地味だけど、区切りだけ変えて桁を放置する事故はけっこう見ます。

③ STRING_AGG の8000バイト制限（Msg 9829）

STRING_AGG は入力列の型で戻り値の型が決まります。入力が varchar(n) / nvarchar(n) みたいな長さ指定の型だと、連結結果は最大8000バイトに制限される。連結対象が多くて8000バイトを超えると、途中で切れるどころか、こういうエラーで落ちる。

Msg 9829, Level 16, State 0
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

「ステージングの少ないデータでは動いてたのに、本番の件数で急に落ちる」やつです。対処は、入力を NVARCHAR(MAX) にキャストして LOB 型に逃がす。

-- 連結結果が長くなる可能性があるなら MAX 型にキャストしておく
SELECT STRING_AGG(CAST(name AS NVARCHAR(MAX)), ', ') WITHIN GROUP (ORDER BY id) AS names
FROM dbo.Members;