【SQL】STRING_AGGでグルーピングした要素をカンマ繋ぎで取得する【SQL Server】 │ コジマノテック

1. はじめに
2. 基本構文
3. 例１：削除されたユーザーIDの一覧を取得したい
- 3.1. サンプルSQL
- 3.2. 出力結果
4. 例２：値段が500円を超える商品名をスラッシュつなぎで出す
- 4.1. サンプルSQL
- 4.2. 出力結果
5. さいごに
6. もっと学びたい人はこちら

はじめに

コジマです。

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

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

こんなときに使います。

基本構文

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

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

例１：削除されたユーザー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

例２：値段が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をもっと学びたいエンジニアのためにおすすめのUdemy講座を紹介いたします！

SQLはじめましての人！まずあなたの３時間をください。
この講座でまずこれだけは覚えてくれ！という内容をしっかり押さえることができます。
SQLはプログラミング言語がなんであれ、システム開発に必要になります。
この講座を見て最低限必要な知識を身に着けて実践で貢献できるようになりましょう！
3時間で学ぶ SQL ・データベース超入門【丁寧な解説+演習問題で SQL データ抽出の基本が身につく】標準 SQL

データベースの設計について学べる講座です。
設計について学べる教材は少ないので、実務でデータベースを触る人にはとてもうれしい講座です。
データベース講座１：データベース論理設計

ちょっとニッチに感じるかもしれませんが、SQLのインデックスを学ぶことができる講座です。
よい性能でSQLを運用するためにはインデックスの理解は必要不可欠です。
CRUD書いて満足するエンジニアを出し抜きましょう。
SQLServerのインデックスを理解して検索を速くする方法

気になった人はぜひ見てみてくださいね！
以上、コジマでした。