SQL Server 2017 の新機能の中に、複数行のデータを1行にまとめて表示することができるSTRING_AGG関数がある。
今回は、STRING_AGG関数を使って、key-value形式で設定されているデータを複数行のデータを1行にまとめて表示してみたので、その手順を共有する。
前提条件
以下の記事に従って、Azure SQL Databaseの作成が完了していること。
Azure Potal上でSQLデータベースを作成してみたAzure Portal上では、常に最新の安定したバージョンの SQL Serverデータベースである「SQL データベース」が利用でき...
また、A5M2からAzure SQL Databaseに接続できていること。その手順は、以下の記事を参考にすること。
A5M2を利用して各DBに接続してみたA5M2(A5:SQL Mk-2)は、SQL文の入力支援やER図作成などの機能を備えていて、Oracle、MySQL、PostgreSQ...
STRING_AGGを利用したSQL実行
STRING_AGGを利用し、複数行のデータを1行にまとめて表示した実行例は、以下の通り。
1) 以下のデータがある状態からスタートする。
SELECT * FROM user_address ORDER BY user_id ASC, user_key ASC, id ASC
2) STRING_AGG関数を使わずに、user_idとuser_key=’address’であるデータを抽出した結果は、以下の通り。
SELECT
user_id
, user_value
FROM user_address
WHERE user_key = 'address'
ORDER BY user_id ASC, id ASC
3) STRING_AGG関数を使って、user_idとuser_key=’address’であるデータを抽出した結果は以下の通りで、2)と同じ結果になる。
SELECT
user_id
, STRING_AGG(CASE user_key WHEN 'address' THEN user_value ELSE NULL END, ',') AS address
FROM user_address
GROUP BY user_id
ORDER BY user_id ASC
4) STRING_AGG関数を使わずに、user_idとuser_key=’mail’であるデータを抽出した結果は以下の通りで、同一user_idのメールアドレスが複数行に表示されることが確認できる。
SELECT
id
, user_id
, user_value
FROM user_address
WHERE user_key = 'mail'
ORDER BY user_id ASC, id ASC
5) STRING_AGG関数を使って、user_idとuser_key=’mail’であるデータを抽出した結果は以下の通りで、同一user_idのメールアドレスが、カンマ区切りで1行に集約し表示されることが確認できる。
SELECT
user_id
, STRING_AGG(CASE user_key WHEN 'mail' THEN user_value ELSE NULL END, ',')
WITHIN GROUP (ORDER BY id ASC) AS mail
FROM user_address
GROUP BY user_id
ORDER BY user_id ASC
6) STRING_AGG関数を使って、user_idと全体のデータを抽出した結果は以下の通りで、user_id毎に1行に集約し表示されることが確認できる。
SELECT
user_id
, STRING_AGG(CASE user_key WHEN 'post' THEN user_value ELSE NULL END, ',') AS post
, STRING_AGG(CASE user_key WHEN 'address' THEN user_value ELSE NULL END, ',') AS address
, STRING_AGG(CASE user_key WHEN 'phone' THEN user_value ELSE NULL END, ',')
WITHIN GROUP (ORDER BY id ASC) AS phone
, STRING_AGG(CASE user_key WHEN 'mail' THEN user_value ELSE NULL END, ',')
WITHIN GROUP (ORDER BY id ASC) AS mail
FROM user_address
GROUP BY user_id
ORDER BY user_id ASC
要点まとめ
- STRING_AGG関数は、複数行のデータを1行にまとめて表示することができる関数で、SQL Server 2017以降のバージョンで利用できる。
