はじめに
複数レコードを１レコードにまとめたいときって、業務上たまにあると思います。
例えば、ユーザーと保有権限についてファイルを出力する時、商品タグを取得する時など。
ほんと数年に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ならそのままコピペしても動くように書きました。
必要に応じてテーブルを書き換えてつかってください。