STRING_AGG (Transact-SQL)

適用範圍:SQL Server 2017 (14.x) 及更新版的 是Azure SQL Database 是Azure SQL 受控執行個體 是Azure Synapse Analytics

能串連字串運算式的值,並在這些值之間放置分隔符號值。 系統不會在字串結尾處加入分隔符號。

主題連結圖示 Transact-SQL 語法慣例

語法

STRING_AGG ( expression, separator ) [ <order_clause> ]

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

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件

引數

expression
這是任何類型的運算式。 在串連期間,運算式會轉換成 NVARCHARVARCHAR 類型。 非字串類型會轉換成 NVARCHAR 類型。

separator
這是 NVARCHARVARCHAR 類型的運算式,用來作為串連字串的分隔符號。 這可以是常值或變數。

<order_clause>
選擇性地使用 WITHIN GROUP 子句指定串連結果的順序:

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

<order_by_expression_list>

非常數運算式的清單,可用來排序結果。 每個查詢只允許一個 order_by_expression。 預設排序順序為遞增。

傳回型別

傳回類型取決於第一個引數 (運算式)。 如果輸入引數是字串類型 (NVARCHARVARCHAR),結果類型將會與輸入類型相同。 下表列出自動轉換:

輸入運算式類型 結果
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 是一種彙總函式,此函數可擷取資料列中的所有運算式,並將它們串連成單一字串。 運算式值會以隱含方式轉換為字串類型,然後再行串連。 隱含轉換成字串會遵循現有的資料類型轉換規則。 如需有關資料類型轉換的詳細資訊,請參閱 CAST 和 CONVERT (Transact-SQL)

如果輸入運算式為 VARCHAR 類型,則分隔符號不得為 NVARCHAR 類型。

系統會忽略 Null 值,而且不會加入對應的分隔符號。 若要傳回 Null 值的預留位置,請使用 ISNULL 函數,如範例 B 中所示。

STRING_AGG 可在任何相容性層級使用。

範例

A. 產生名稱的清單,並以新行分隔

下列範例會在單一結果資料格中產生一份名稱的清單,並以歸位字元分隔這些名稱。

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

以下為結果集。

csv
Syed
Catherine
Kim
Kim
Kim
Hazem
...

name 資料格中找到的 NULL 值不會在結果中傳回。

注意

如果使用 SQL Server Management Studio 查詢編輯器,[以方格顯示結果] 選項將無法實作歸位字元。 請切換至 [以文字顯示結果] 以正確地查看結果集。
根據預設,[以文字顯示結果] 會截斷為 256 個字元。 若要增加此限制,請變更 [每個資料行中顯示的最大字元數] 選項。

B. 產生以逗號分隔且不含 NULL 值的名稱清單

下列範例會將 Null 值取代為 'N/A',並在單一結果資料格中傳回以逗號分隔的名稱。

USE AdventureWorks2016
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(FirstName,'N/A')), ',') AS csv 
FROM Person.Person; 

以下為結果集。

注意

會顯示修剪過的結果。

csv
Syed,Catherine,Kim,Kim,Kim,Hazem,Sam,Humberto,Gustavo,Pilar,Pilar, ...

C. 產生以逗號分隔的值

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

以下為結果集。

注意

會顯示修剪過的結果。

名稱
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)
...

注意

如果使用 Management Studio 查詢編輯器,[以方格顯示結果] 選項將無法實作歸位字元。 請切換至 [以文字顯示結果] 以正確地查看結果集。

文件及其標籤會分成不同的資料表。 開發人員希望針對每篇文章傳回單一資料列,並提供所有相關的標籤。 使用下列查詢:

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;

以下為結果集。

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

注意

STRING_AGG 函式不是 SELECT 清單中的唯一項目,則 GROUP BY 子句為必要項目。

E. 產生每個鄉鎮的電子郵件清單

下列查詢會尋找員工的電子郵件地址,並依城市分組:

USE AdventureWorks2016
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;

以下為結果集。

注意

會顯示修剪過的結果。

城市 電子郵件
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
Bellevue 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;...
Bellingham christopher23@adventure-works.com;frederick7@adventure-works.com;omar0@adventure-works.com;...

在電子郵件資料行中傳回的電子郵件,可以直接用來傳送電子郵件給在一些特定城市工作的人員群組。

F. 產生每個鄉鎮的排序電子郵件清單

與上一個範例相類似,下列查詢會尋找員工的電子郵件地址,依城市分組,並按字母順序排序電子郵件:

USE AdventureWorks2016
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;

以下為結果集。

注意

會顯示修剪過的結果。

城市 電子郵件
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;...

另請參閱

CONCAT (Transact-SQL)
CONCAT_WS (Transact-SQL)
FORMATMESSAGE (Transact-SQL)
QUOTENAME (Transact-SQL)
REPLACE (Transact-SQL)
REVERSE (Transact-SQL)
STRING_ESCAPE (Transact-SQL)
STUFF (Transact-SQL)
TRANSLATE (Transact-SQL)
彙總函數 (Transact-SQL)
字串函數 (Transact-SQL)