SQLでカンマ区切りやcsvに使える！ STRING_AGG関数の活用法

SQLを組み立てるときにカンマ区切りのCSVデータを作りたい、区切り文字を付けてデータを集約したい、などなどの状況に対してさまざまなアプローチがありました。

カンマ区切りのデータリスト作成に時間がかかり、手間がかかる。
CSV形式でのデータエクスポートが必要な場合、独自のプログラミングが必須。
データの集約やリスト化に、複雑なSQL文やプログラムの記述を要する。
従来のFOR XML PATHを用いた方法では、XMLのマークアップを避けながらデータをカンマ区切りで集約する「裏技」を使う必要があるが、これは直感的ではなく、可読性や保守性に欠ける。

これらの課題は、データ管理プロセスの効率性を著しく低下させ、特に大量のデータを扱う場合には、その影響はさらに大きくなります。しかし、SQLのSTRING_AGG関数を使うことで、これらの問題は一挙に解決します

STRING_AGG関数を使用することで、単一のクエリでカンマ区切りのデータリストを簡単に作成できる。
独自のプログラミングや複雑なクエリを記述することなく、CSV形式のデータエクスポートが可能になる。
FOR XML PATHを用いた複雑なSQLに代わり、直感的で読みやすい方法でデータを集約できる。

実践的な具体例

実践的な具体例

チームやプロジェクトのメンバーリスト

プロジェクトごとに所属メンバーを一覧表示することは、プロジェクト管理やチームコミュニケーションにおいて非常に有用です。チームの構成を把握しやすくすることで、プロジェクトの進行状況の報告やリソース管理を効率的に行うことができます。

テーブル構造:ProjectMembers

ColumnName	DataType	Description
ProjectID	INT	プロジェクトID
MemberName	VARCHAR(50)	メンバー名
Role	VARCHAR(50)	役割

サンプルデータ:

ProjectID	MemberName	Role
1	山田	デザイナー
1	佐々木	エンジニア
2	鈴木	マネージャー
2	伊藤	エンジニア

SQLクエリ:

sqlCopy code

SELECT
  ProjectID,
  STRING_AGG(MemberName, ', ') WITHIN GROUP (
    ORDER BY
      MemberName
  ) AS ProjectMembers
FROM
  ProjectMembers
GROUP BY
  ProjectID;

SELECT

ProjectID,

STRING_AGG(MemberName, ', ') WITHIN GROUP (

ORDER BY

MemberName

) AS ProjectMembers

FROM

ProjectMembers

GROUP BY

ProjectID;

出力結果:

ProjectID	ProjectMembers
1	山田, 佐々木
2	伊藤, 鈴木

この方法は、プロジェクトの進行状況報告やチームの構成確認に有用です。一目でどのメンバーがどのプロジェクトに参加しているかが分かり、プロジェクトマネージャーやチームリーダーが効率的に情報を共有できます。

メールや通知のアドレスリスト

部門ごとに特定の情報を共有するためには、関連する従業員のメールアドレスを迅速に把握することが必要です。STRING_AGG関数を使用して、部門ごとに従業員のメールアドレスを一覧化する方法を紹介します。

テーブル構造:Employees

ColumnName	DataType	Description
EmployeeID	INT	従業員ID
Email	VARCHAR(100)	メールアドレス
DepartmentID	INT	部門ID

サンプルデータ:

EmployeeID	Email	DepartmentID
1	yamada@example.com	1
2	sasaki@example.com	1
3	suzuki@example.com	2

SQLクエリ:

SELECT
  DepartmentID,
  STRING_AGG(Email, '; ') WITHIN GROUP (
    ORDER BY
      Email
  ) AS EmailList
FROM
  Employees
GROUP BY
  DepartmentID;

SELECT

DepartmentID,

STRING_AGG(Email, '; ') WITHIN GROUP (

ORDER BY

) AS EmailList

FROM

Employees

GROUP BY

DepartmentID;

出力結果:

DepartmentID	EmailList
1	yamada@example.com; sasaki@example.com
2	suzuki@example.com

この方法は、HR部門やプロジェクト管理チームが部門ごとに従業員に連絡を取る際に非常に便利です。メールアドレスを手動で集める手間が省け、時間の節約につながります。

顧客や製品ごとの関連情報の集約

顧客が購入した製品のリストを把握することは、マーケティング戦略やカスタマーサポートの質を向上させる上で重要です。STRING_AGGを利用して、顧客ごとの購入製品リストを作成します。

テーブル構造:CustomerPurchases

ColumnName	DataType	Description
CustomerID	INT	顧客ID
ProductName	VARCHAR(100)	製品名

サンプルデータ:

CustomerID	ProductName
1	製品A
1	製品B
2	製品C

SQLクエリ:

SELECT
  CustomerID,
  STRING_AGG(ProductName, ', ') WITHIN GROUP (
    ORDER BY
      ProductName
  ) AS PurchasedProducts
FROM
  CustomerPurchases
GROUP BY
  CustomerID;

SELECT

CustomerID,

STRING_AGG(ProductName, ', ') WITHIN GROUP (

ORDER BY

ProductName

) AS PurchasedProducts

FROM

CustomerPurchases

GROUP BY

CustomerID;

出力結果:

CustomerID	PurchasedProducts
1	製品A, 製品B
2	製品C

顧客の購買履歴を一覧表示することで、個々の顧客に合わせたカスタマイズされたマーケティング戦略を立てることが可能になります。また、カスタマーサポートが顧客からの問い合わせに対応する際に、購入製品の情報を迅速に確認できます。

WebインターフェースやAPI

ウェブアプリケーションやAPIでは、ユーザーに対して関連するデータを効率的に提示することが重要です。STRING_AGGを使用して、ユーザーに関連する情報を一つの文字列として整形し、API応答などで返す方法を紹介します。

テーブル構造: UserActivities

ColumnName	DataType	Description
UserID	INT	ユーザーID
Activity	VARCHAR(100)	アクティビティ名

サンプルデータ:

UserID	Activity
1	ログイン
1	購入
2	ログイン
2	コメント投稿

SQLクエリ:

SELECT
  UserID,
  STRING_AGG(Activity, ', ') WITHIN GROUP (
    ORDER BY
      Activity
  ) AS Activities
FROM
  UserActivities
GROUP BY
  UserID;

SELECT

UserID,

STRING_AGG(Activity, ', ') WITHIN GROUP (

ORDER BY

Activity

) AS Activities

FROM

UserActivities

GROUP BY

UserID;

出力結果:

UserID	Activities
1	ログイン, 購入
2	コメント投稿, ログイン

この方法は、ユーザーの行動履歴やアクティビティを一覧表示するウェブインターフェースや、ユーザープロファイルの概要を提供するAPIにとって非常に有用です。ユーザーに関連するデータをコンパクトな形式で返すことで、フロントエンドの処理を軽減し、ユーザーエクスペリエンスを向上させます。

おすすめ情報

ある程度SQLに慣れてくるとより複雑なSQLを組むようになり、分析関数（ウインドウ関数）の壁にぶち当たります。本書は私が初級から抜け出すのになったきっかけの本です。比較的新しい関数なので古いSQLの本には載ってません。昔ながらのSQLを書く人はウインドウ関数の理解に苦しむケースが多いと思います。一歩先に進むためにも苦手意識を克服したいですね。