はじめに

複数レコードを１レコードにまとめたいときって、業務上たまにあると思います。

例えば、ユーザーと保有権限についてファイルを出力する時、商品タグを取得する時など。
ほんと数年に1回ぐらい・・・

そういう時、STRING_AGG()を使うと楽にできるので、備忘録代わりに残しておきます。

STRING_AGG()の使用例

カンマ区切りで出力する

以下のようにusersとpermissionsの2つのテーブルを結合して、
permissions.permission_nameをカンマ区切りで取得する。

sql1 : 権限をカンマでつなげる

SELECT
	users
	.userid
	,users.name
	,STRING_AGG(permissions.permission_name, ',') AS permissions
FROM
	(VALUES (1,'Taro'),(2,'Jiro'),(3,'Hiroshi')
	) as users(userid,name)
INNER JOIN
	(VALUES (1,'参照権限'),(1,'編集権限'),(2,'参照権限')
	) as permissions(userid,permission_name) ON users.userid = permissions.userid
GROUP BY users.userid,users.name

userid      name    permissions
----------- ------- -----------------
1           Taro    参照権限,編集権限
2           Jiro    参照権限

(2 行処理されました)

ソートする

WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )をつけることでソートする。

sql2 : sql1の権限を逆に並べてカンマでつなげる

SELECT
	users
	.userid
	,users.name
	,STRING_AGG(ISNULL(permissions.permission_name,'なし'), ',')
		WITHIN GROUP (ORDER BY permission_name DESC) AS permissions
FROM
	(VALUES (1,'Taro'),(2,'Jiro'),(3,'Hiroshi')
	) as users(userid,name)
INNER JOIN
	(VALUES (1,'参照権限'),(1,'編集権限'),(2,'参照権限')
	) as permissions(userid,permission_name) ON users.userid = permissions.userid
GROUP BY users.userid,users.name

userid      name    permissions
----------- ------- -----------------
1           Taro    編集権限,参照権限
2           Jiro    参照権限

(2 行処理されました)

NULLを置換する

NULLを置換することもできます。

sql2 : sql1で権限のないuserid=3を外部結合で取得する

SELECT
	users
	.userid
	,users.name
	,STRING_AGG(ISNULL(permissions.permission_name,'なし'), ',') AS permissions
FROM
	(VALUES (1,'Taro'),(2,'Jiro'),(3,'Hiroshi')
	) as users(userid,name)
LEFT JOIN
	(VALUES (1,'参照権限'),(1,'編集権限'),(2,'参照権限')
	) as permissions(userid,permission_name) ON users.userid = permissions.userid
GROUP BY users.userid,users.name

userid      name    permissions
----------- ------- -----------------
1           Taro    参照権限,編集権限
2           Jiro    参照権限
3           Hiroshi なし

(3 行処理されました)

おわりに

SQLServerならそのままコピペしても動くように書きました。
必要に応じてテーブルを書き換えてつかってください。