SQL Serverでデータを操作する際、特定のレコード群から派生した文字列を連結して一つのカラムにまとめたい場合があります。
本記事では、FOR XML PATHとSTUFF関数を使用して、このような文字列の連結を行う方法を紹介します。
テーブルの作成
ブログエントリーとコメントを保存するための2つのテーブルを作成します。
-- ブログテーブルの作成
CREATE TABLE blogs (
blog_id INT PRIMARY KEY,
title NVARCHAR(255),
content NVARCHAR(MAX)
);
-- コメントテーブルの作成
CREATE TABLE comments (
comment_id INT PRIMARY KEY,
blog_id INT,
title NVARCHAR(255),
comment NVARCHAR(MAX)
);
サンプルデータの挿入
次に、テスト用のサンプルデータを挿入します。
-- ブログデータの挿入
INSERT INTO blogs (blog_id, title, content) VALUES
(1, N'SQL Serverの魅力', N'SQL Serverについての深い解説。'),
(2, N'データベースの最適化', N'パフォーマンスの向上について。');
-- コメントデータの挿入
INSERT INTO comments (comment_id, blog_id, title, comment) VALUES
(1, 1, N'素晴らしい記事です', N'非常に役立ちました。'),
(2, 1, N'同意します', N'特にインデックスの部分が参考になりました。'),
(3, 2, N'もっと詳しく', N'特定の最適化技術についてもっと知りたいです。');
各ブログエントリーについて、それに関連するコメントのタイトルをカンマ区切りで一つのカラムにまとめるクエリを実行します。
SELECT
b.blog_id,
b.title AS blog_title,
STUFF((SELECT ', ' + c.title
FROM comments c
WHERE c.blog_id = b.blog_id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS comment_titles
FROM
blogs b
GROUP BY
b.blog_id, b.title;
実行結果
このクエリでは、各ブログに対するコメントのタイトルがカンマ区切りで連結され、commentsテーブルの各エントリについてblogsテーブルの関連するエントリと共に表示されます。STUFF関数は、最初のカンマを除去するために使用されています。