STRING_AGG (Transact-SQL)
適用対象:
SQL Server 2017 (14.x) 以降 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウス
文字列式の値を連結し、値の間に区切り記号を挿入します。 文字列の末尾に区切り記号は追加されません。
構文
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
Note
SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。
引数
式 (expression)
任意のデータ型の式を指定します。 連結時に式は NVARCHAR または VARCHAR 型に変換されます。 文字列以外の型は NVARCHAR 型に変換されます。
separator
連結される文字列の区切り記号として使用される NVARCHAR または VARCHAR 型の式です。 リテラルまたは変数を使用できます。
<order_clause>
必要に応じて、WITHIN GROUP 句を使用して連結結果の順序を指定します。
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
<order_by_expression_list>
結果を並べ替えるために使用できる、定数ではない式のリスト。 クエリごとに 1 つの order_by_expression のみを使用できます。 既定の並べ替え順は昇順です。
戻り値の型
戻り値の型は、最初の引数 (式) に依存します。 入力の引数が文字列型 (NVARCHAR、VARCHAR) の場合、結果の型は入力の型と同じになります。 次の表は自動変換の一覧です。
| 入力式の型 | 結果 |
|---|---|
| NVARCHAR(MAX) | NVARCHAR(MAX) |
| VARCHAR(MAX) | VARCHAR(MAX) |
| NVARCHAR(1...4000) | NVARCHAR(4000) |
| VARCHAR(1...8000) | VARCHAR(8000) |
| int、bigint、smallint、tinyint、numeric、float、real、bit、decimal、smallmoney、money、datetime、datetime2、 | NVARCHAR(4000) |
注釈
STRING_AGG は、すべての式を行から取り出し、それらを 1 つの文字列に連結する集計関数です。 式の値は、暗黙的に文字列型に変換され、連結されます。 文字列への暗黙の変換は、データ型変換の既存の規則に従います。 データ型の変換の詳細については、「CAST および CONVERT (Transact-SQL)」を参照してください。
入力式が VARCHAR 型の場合、区切り記号を NVARCHAR 型にすることはできません。
null 値は無視され、対応する区切り記号は追加されません。 null 値のプレースホルダーを返すには、例 B を参照して ISNULL 関数を使用します。
STRING_AGG は任意の互換性レベルで使用できます。
Note
<order_clause> は、データベース互換性レベル 110 以上で使用できます。
使用例
この記事のほとんどの例では、AdventureWorks サンプル データベースを参照しています。
A. 新しい行で区切られた名前のリストを生成する
次の例では、復帰文字で区切られた名前のリストを単一の結果セルに生成します。
USE AdventureWorks2022;
GO
SELECT STRING_AGG (CONVERT(NVARCHAR(max),FirstName), CHAR(13)) AS csv
FROM Person.Person;
GO
結果セットは次のようになります。
| csv |
|---|
| Syed Catherine Kim Kim Kim Hazem ... |
name セルにある NULL 値は結果で返されません。
Note
SQL Server Management Studio のクエリ エディターを使用している場合、 [結果をグリッドに表示] オプションで復帰文字を実装することはできません。 結果セットを正しく表示するには、[結果をテキストで表示] に切り替えてください。
[結果をテキストで表示] は既定では、256 文字に切り詰められます。 この制限を引き上げるには、 [各列に表示される最大文字数] オプションを変更します。
B. NULL 値を含まない、コンマ区切りの名前のリストを生成する
次の例では、null 値を 'N/A' に置き換え、コンマで区切った名前を 1 つの結果セルに返します。
USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(FirstName,'N/A')), ',') AS csv
FROM Person.Person;
GO
結果セットは次のようになります。
Note
結果はトリミングされて表示されます。
| csv |
|---|
| Syed,Catherine,Kim,Kim,Kim,Hazem,Sam,Humberto,Gustavo,Pilar,Pilar, ... |
C. コンマ区切り値を生成する
USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), CONCAT(FirstName, ' ', LastName, '(', ModifiedDate, ')')), CHAR(13)) AS names
FROM Person.Person;
GO
結果セットは次のようになります。
Note
結果はトリミングされて表示されます。
| 名前 |
|---|
| Ken Sánchez (Feb 8 2003 12:00AM) Terri Duffy (Feb 24 2002 12:00AM) Roberto Tamburello (Dec 5 2001 12:00AM) Rob Walters (Dec 29 2001 12:00AM) ... |
Note
Management Studio のクエリ エディターを使用している場合、[結果をグリッドに表示] オプションで復帰文字を実装できません。 結果セットを正しく表示するには、[結果をテキストで表示] に切り替えてください。
D. 関連するタグが付いたニュース記事を返す
記事とそのタグが異なるテーブルに分かれているデータベースがあると想定します。 開発者は、すべての関連するタグが付いた記事ごとに 1 つの行を返したいと考えています。 次のクエリでは、この結果が得られます。
SELECT a.articleId, title, STRING_AGG (tag, ',') as tags
FROM dbo.Article AS a
LEFT JOIN dbo.ArticleTag AS t
ON a.ArticleId = t.ArticleId
GROUP BY a.articleId, title;
GO
結果セットは次のようになります。
| articleId | title | tags |
|---|---|---|
| 172 | Polls indicate close election results | politics,polls,city council |
| 176 | New highway expected to reduce congestion | NULL |
| 177 | Dogs continue to be more popular than cats | polls,animals |
Note
STRING_AGG 関数が SELECT 一覧内の唯一の項目ではない場合は、GROUP BY 句が必要です。
E. 町ごとの電子メール アドレスのリストを生成する
次のクエリでは、従業員の電子メール アドレスが検索され、市区町村ごとにグループ化されます。
USE AdventureWorks2022;
GO
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') AS emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
GO
結果セットは次のようになります。
Note
結果はトリミングされて表示されます。
| City | emails |
|---|---|
| Ballard | paige28@adventure-works.com;joshua24@adventure-works.com;javier12@adventure-works.com;... |
| Baltimore | gilbert9@adventure-works.com |
| Barstow | kristen4@adventure-works.com |
| Basingstoke Hants | dale10@adventure-works.com;heidi9@adventure-works.com |
| Baytown | kelvin15@adventure-works.com |
| Beaverton | billy6@adventure-works.com;dalton35@adventure-works.com;lawrence1@adventure-works.com;... |
| Bell Gardens | christy8@adventure-works.com |
| ベルビュー | min0@adventure-works.com;gigi0@adventure-works.com;terry18@adventure-works.com;... |
| Bellflower | philip0@adventure-works.com;emma34@adventure-works.com;jorge8@adventure-works.com;... |
| ベリンガム | christopher23@adventure-works.com;frederick7@adventure-works.com;omar0@adventure-works.com;... |
emails 列に返された電子メール アドレスは、特定の市区町村で働く従業員のグループに電子メールを送信する場合にそのまま使用できます。
F. 町ごとの電子メール アドレスの並べ替えられたリストを生成する
次のクエリは、前の例と同様に、従業員の電子メール アドレスを検索し、市区町村ごとにグループ化し、電子メール アドレスをアルファベット順に並べ替えます。
USE AdventureWorks2022;
GO
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') WITHIN GROUP (ORDER BY EmailAddress ASC) AS Emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
GO
結果セットは次のようになります。
Note
結果はトリミングされて表示されます。
| City | メール |
|---|---|
| Barstow | kristen4@adventure-works.com |
| Basingstoke Hants | dale10@adventure-works.com;heidi9@adventure-works.com |
| Braintree | mindy20@adventure-works.com |
| Bell Gardens | christy8@adventure-works.com |
| Byron | louis37@adventure-works.com |
| Bordeaux | ranjit0@adventure-works.com |
| Carnation | don0@adventure-works.com;douglas0@adventure-works.com;george0@adventure-works.com;... |
| Boulogne-Billancourt | allen12@adventure-works.com;bethany15@adventure-works.com;carl5@adventure-works.com;... |
| Berkshire | barbara41@adventure-works.com;brenda4@adventure-works.com;carrie14@adventure-works.com;... |
| Berks | adriana6@adventure-works.com;alisha13@adventure-works.com;arthur19@adventure-works.com;... |
次の手順
次の記事では、Transact-SQL 関数の詳細について説明します。
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示