【SQL】STRING_AGGでグルーピングした要素をカンマ繋ぎで取得する【SQL Server】

  • 2020.08.05
  • SQL
【SQL】STRING_AGGでグルーピングした要素をカンマ繋ぎで取得する【SQL Server】

はじめに

コジマです。

削除されたユーザーIDの一覧を取得したい!
特定の条件を満たすアイテムIDを取得したい!

でも縦に並んでるのはヤダ!カンマ繋ぎがいい!

こんなときに使います。

基本構文

STRING_AGG (カラム名, 区切り文字) WITHIN GROUP (ORDER BY ソート条件)

です。もっと細かいものもあるけれど、これがわかればよいです。
区切り文字を「,」にすることでカンマ区切りの集約を実現するってわけです。

例1:削除されたユーザーIDの一覧を取得したい

サンプルSQL

WITH test AS (
    /* is_deleted:0 有効ユーザ、1 無効ユーザ とする */
    SELECT 1 AS id, 'kojima' AS name, 0 AS is_deleted
    UNION
    SELECT 2 , 'yoshida', 0
    UNION
    SELECT 3, 'tanaka', 1
    UNION
    SELECT 4, 'suzuki', 1
    UNION
    SELECT 5, 'nishida', 0
)
SELECT
    -- 3, 4
    STRING_AGG(id, ',') WITHIN GROUP (ORDER BY id) AS ids
    -- tanaka, suzuki
    ,STRING_AGG(name, ',') WITHIN GROUP (ORDER BY id) AS names
FROM test
WHERE is_deleted = 1 -- 削除したユーザのみ取得
GROUP BY is_deleted -- 削除フラグで集約
;

出力結果

id name
3,4 tanaka,suzuki

例2:値段が500円を超える商品名をスラッシュつなぎで出す

区切り文字変えればスラッシュつなぎだってできます。

サンプルSQL

WITH items AS (
    /* 値段が500円を超える商品名をスラッシュつなぎで出す */
    SELECT 1 AS id, 'けん玉' AS item_name, 500 AS price, 'A会社' AS company
    UNION
    SELECT 2 , 'こま', 200, 'B会社'
    UNION
    SELECT 3, 'かるた', 1000, 'A会社'
    UNION
    SELECT 4, '花札', 800, 'B会社'
    UNION
    SELECT 5, 'めんこ', 300, 'A会社'
    UNION
    SELECT 6, 'だるま', 1200, 'B会社'
)
SELECT
    -- かるた/花札/だるま
    STRING_AGG(item_name, '/') WITHIN GROUP (ORDER BY id) AS names
FROM items
WHERE price > 500 -- 500円を超える
;

出力結果

names
かるた/花札/だるま

さいごに

こういう小技を覚えるとSQL書くのが楽しくなってきます。

STRING_AGGの公式ドキュメントはこちら。
https://docs.microsoft.com/ja-jp/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

SQL Serverで使えるようになったのはSQL Server2017からとのことなので、古いバージョンでは使えません。

他のRDSで使えるかは分かりませんが、バージョンの注意は必要です。

この記事を面白いまたは役に立ったと思ってくれた方は是非私のTwitter(@kojimanotech)を
フォローしてくれたらうれしいです!

以上、コジマでした。


SQLカテゴリの最新記事