行データをカンマ区切りの文字列に変換する（FOR XML PATH）

SQL Serverで複数の行データをカンマ区切り（,）の文字列に変換する方法です。仕事で必要になりそうだったので調べてみましたが、最終的にSQLでやる必要はなくなりました。ただ、せっかくなので、メモとして残しておきます。

今回は、以下のテーブル構成とデータを対象に実行します。

テーブル１「example_tbl」

id	kind_id	name
1	1	A1
2	1	A2
3	1	A3
4	2	B1
5	2	B2
6	3	C1
7	3	NULL
8	3	NULL
9	4	NULL
10	4	NULL

テーブル２「kind_tbl」

id	name
1	A
2	B
3	C
4	D

SQLは以下の通りです。

SELECT
id,
kind,
CASE WHEN LEN(comma_name) > 0 THEN
	LEFT(comma_name, charindex(',', comma_name, LEN(comma_name)) - 1)
ELSE
	NULL
END comma_name

FROM
(
	SELECT 
	id,
	name AS kind,
	(
		SELECT name + ','
		FROM example_tbl
		WHERE example_tbl.kind_id = kind_tbl.id
		ORDER BY id
		FOR XML PATH('')
	) AS comma_name

	FROM 
	kind_tbl
) a

上記のSQLでは、サブクエリでカンマ区切りの文字列を生成して、最終的なSELECTの「CASE WHEN ～ THEN」で一番後ろのカンマを削除しています。

上記のSQLを実行した結果は以下の通りです。

id	kind	comma_name
1	A	A1,A2,A3
2	B	B1,B2
3	C	C1
4	D	NULL