STRING_AGG (Transact-SQL)

適用対象: SQL Server 2017 (14.x) 以降 Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Microsoft Fabric のSQL 分析エンドポイント Microsoft Fabric のウェアハウス

文字列式の値を連結し、値の間に区切り記号を挿入します。 文字列の末尾に区切り記号は追加されません。

Transact-SQL 構文表記規則

構文

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )   

引数

式 (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) の場合、結果の型は入力の型と同じになります。 次の表は自動変換の一覧です。

注釈

STRING_AGG は、すべての式を行から取り出し、それらを 1 つの文字列に連結する集計関数です。 式の値は、暗黙的に文字列型に変換され、連結されます。 文字列への暗黙の変換は、データ型変換の既存の規則に従います。 データ型の変換の詳細については、「CAST および CONVERT (Transact-SQL)」を参照してください。

入力式が VARCHAR 型の場合、区切り記号を NVARCHAR 型にすることはできません。

null 値は無視され、対応する区切り記号は追加されません。 null 値のプレースホルダーを返すには、例 B を参照して ISNULL 関数を使用します。

STRING_AGG は任意の互換性レベルで使用できます。

使用例

この記事のほとんどの例では、AdventureWorks サンプル データベースを参照しています。

A. 新しい行で区切られた名前のリストを生成する

次の例では、復帰文字で区切られた名前のリストを単一の結果セルに生成します。

USE AdventureWorks2022;
GO
SELECT STRING_AGG (CONVERT(NVARCHAR(max),FirstName), CHAR(13)) AS csv 
FROM Person.Person;
GO

結果セットは次のとおりです。

name セルにある NULL 値は結果で返されません。

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

結果セットは次のとおりです。

C. コンマ区切り値を生成する

USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), CONCAT(FirstName, ' ', LastName, '(', ModifiedDate, ')')), CHAR(13)) AS names 
FROM Person.Person;
GO

結果セットは次のとおりです。

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

結果セットは次のとおりです。

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

結果セットは次のとおりです。

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

結果セットは次のとおりです。

次の手順

次の記事では、Transact-SQL 関数の詳細について説明します。

• STRING_ESCAPE (Transact-SQL)
• STUFF (Transact-SQL)
• CONCAT (Transact-SQL)
• CONCAT_WS (Transact-SQL)
• 集計関数 (Transact-SQL)
• 文字列関数 (Transact-SQL)