Azure SQL DatabaseでSTRING_AGG関数を利用してみた｜ITエンジニアとして経験・学習したこと

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