STRING_AGG (Transact-SQL)STRING_AGG (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

能串連字串運算式的值,並在這些值之間放置分隔符號值。Concatenates the values of string expressions and places separator values between them. 系統不會在字串結尾處加入分隔符號。The separator is not added at the end of string.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

STRING_AGG ( expression, separator ) [ <order_clause> ]

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

引數Arguments

expressionexpression
這是任何類型的運算式Is an expression of any type. 在串連期間,運算式會轉換成 NVARCHARVARCHAR 類型。Expressions are converted to NVARCHAR or VARCHAR types during concatenation. 非字串類型會轉換成 NVARCHAR 類型。Non-string types are converted to NVARCHAR type.

separatorseparator
這是 NVARCHARVARCHAR 類型的運算式,用來作為串連字串的分隔符號。Is an expression of NVARCHAR or VARCHAR type that is used as separator for concatenated strings. 這可以是常值或變數。It can be literal or variable.

<order_clause><order_clause>
選擇性地使用 WITHIN GROUP 子句指定串連結果的順序:Optionally specify order of concatenated results using WITHIN GROUP clause:

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

<order_by_expression_list><order_by_expression_list>

非常數運算式的清單,可用來排序結果。A list of non-constant expressions that can be used for sorting results. 每個查詢只允許一個 order_by_expressionOnly one order_by_expression is allowed per query. 預設排序順序為遞增。The default sort order is ascending.

傳回類型Return Types

傳回類型取決於第一個引數 (運算式)。Return type is depends on first argument (expression). 如果輸入引數是字串類型 (NVARCHARVARCHAR),結果類型將會與輸入類型相同。If input argument is string type (NVARCHAR, VARCHAR), result type will be same as input type. 下表列出自動轉換:The following table lists automatic conversions:

輸入運算式類型Input expression type 結果Result
NVARCHAR(MAX)NVARCHAR(MAX) NVARCHAR(MAX)NVARCHAR(MAX)
VARCHAR(MAX)VARCHAR(MAX) VARCHAR(MAX)VARCHAR(MAX)
NVARCHAR(1...4000)NVARCHAR(1...4000) NVARCHAR(4000)NVARCHAR(4000)
VARCHAR(1...8000)VARCHAR(1...8000) VARCHAR(8000)VARCHAR(8000)
int、bigint、smallint、tinyint、numeric、float、real、bit、decimal、smallmoney、money、datetime、datetime2int, bigint, smallint, tinyint, numeric, float, real, bit, decimal, smallmoney, money, datetime, datetime2, NVARCHAR(4000)NVARCHAR(4000)

RemarksRemarks

STRING_AGG 是一種彙總函式,此函數可擷取資料列中的所有運算式,並將它們串連成單一字串。STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. 運算式值會以隱含方式轉換為字串類型,然後再行串連。Expression values are implicitly converted to string types and then concatenated. 隱含轉換成字串會遵循現有的資料類型轉換規則。The implicit conversion to strings follows the existing rules for data type conversions. 如需有關資料類型轉換的詳細資訊,請參閱 CAST 和 CONVERT (Transact-SQL)For more information about data type conversions, see CAST and CONVERT (Transact-SQL).

如果輸入運算式為 VARCHAR 類型,則分隔符號不得為 NVARCHAR 類型。If the input expression is type VARCHAR, the separator cannot be type NVARCHAR.

系統會忽略 Null 值,而且不會加入對應的分隔符號。Null values are ignored and the corresponding separator is not added. 若要傳回 Null 值的預留位置,請使用 ISNULL 函數,如範例 B 中所示。To return a place holder for null values, use the ISNULL function as demonstrated in example B.

STRING_AGG 可在任何相容性層級使用。STRING_AGG is available in any compatibility level.

範例Examples

A.A. 產生名稱的清單,並以新行分隔Generate list of names separated in new lines

下列範例會在單一結果資料格中產生一份名稱的清單,並以歸位字元分隔這些名稱。The following example produces a list of names in a single result cell, separated with carriage returns.

SELECT STRING_AGG (FirstName, CHAR(13)) AS csv 
FROM Person.Person; 

以下為結果集:Here is the result set.

csvcsv
SyedSyed
CatherineCatherine
KimKim
KimKim
KimKim
HazemHazem
......

name 資料格中找到的 NULL 值不會在結果中傳回。NULL values found in name cells are not returned in result.

注意

如果使用 Management Studio 查詢編輯器,[以方格顯示結果] 選項將無法實作歸位字元。If using the Management Studio Query Editor, the Results to Grid option cannot implement the carriage return. 請切換至 [以文字顯示結果] 以正確地查看結果集。Switch to Results to Text to see the result set properly.

B.B. 產生以逗號分隔且不含 NULL 值的名稱清單Generate list of names separated with comma without NULL values

下列範例會將 Null 值取代為 'N/A',並在單一結果資料格中傳回以逗號分隔的名稱。The following example replaces null values with 'N/A' and returns the names separated by commas in a single result cell.

SELECT STRING_AGG ( ISNULL(FirstName,'N/A'), ',') AS csv 
FROM Person.Person; 

以下為結果集:Here is the result set.

CsvCsv
John,N/A,Mike,Peter,N/A,N/A,Alice,BobJohn,N/A,Mike,Peter,N/A,N/A,Alice,Bob

C.C. 產生以逗號分隔的值Generate comma-separated values

SELECT 
STRING_AGG(CONCAT(FirstName, ' ', LastName, ' (', ModifiedDate, ')'), CHAR(13)) 
  AS names 
FROM Person.Person; 

以下為結果集:Here is the result set.

名稱names
Ken Sánchez (Feb 8 2003 12:00AM)Ken Sánchez (Feb 8 2003 12:00AM)
Terri Duffy (Feb 24 2002 12:00AM)Terri Duffy (Feb 24 2002 12:00AM)
Roberto Tamburello (Dec 5 2001 12:00AM)Roberto Tamburello (Dec 5 2001 12:00AM)
Rob Walters (Dec 29 2001 12:00AM)Rob Walters (Dec 29 2001 12:00AM)
......

注意

如果使用 Management Studio 查詢編輯器,[以方格顯示結果] 選項將無法實作歸位字元。If using the Management Studio Query Editor, the Results to Grid option cannot implement the carriage return. 請切換至 [以文字顯示結果] 以正確地查看結果集。Switch to Results to Text to see the result set properly.

文件及其標籤會分成不同的資料表。Article and their tags are separated into different tables. 開發人員希望針對每篇文章傳回單一資料列,並提供所有相關的標籤。Developer wants to return one row per each article with all associated tags. 使用下列查詢:Using following query:

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;

以下為結果集:Here is the result set.

articleIdarticleId titletitle 標記tags
172172 Polls indicate close election resultsPolls indicate close election results politics,polls,city councilpolitics,polls,city council
176176 New highway expected to reduce congestionNew highway expected to reduce congestion NULLNULL
177177 Dogs continue to be more popular than catsDogs continue to be more popular than cats polls,animalspolls,animals

注意

STRING_AGG 函式不是 SELECT 清單中的唯一項目,則 GROUP BY 子句為必要項目。The GROUP BY clause is required if the STRING_AGG function isn't the only item in the SELECT list.

E.E. 產生每個鄉鎮的電子郵件清單Generate list of emails per towns

下列查詢會尋找員工的電子郵件地址,並依鄉鎮分組:The following query finds the email addresses of employees and groups them by towns:

SELECT town, STRING_AGG (email, ';') AS emails 
FROM dbo.Employee 
GROUP BY town; 

以下為結果集:Here is the result set.

鄉鎮town 電子郵件emails
SeattleSeattle syed0@adventure-works.com;catherine0@adventure-works.com;kim2@adventure-works.comsyed0@adventure-works.com;catherine0@adventure-works.com;kim2@adventure-works.com
LALA sam1@adventure-works.com;hazem0@adventure-works.comsam1@adventure-works.com;hazem0@adventure-works.com

在電子郵件資料行中傳回的電子郵件,可以直接用來傳送電子郵件給於部分特定鄉鎮工作的人員群組。Emails returned in the emails column can be directly used to send emails to group of people working in some particular towns.

F.F. 產生每個鄉鎮的排序電子郵件清單Generate a sorted list of emails per towns

與上一個範例類似,下列查詢會尋找員工的電子郵件地址,依鄉鎮分組,並以字母順序排序電子郵件:Similar to previous example, the following query finds the email addresses of employees, groups them by town, and sorts the emails alphabetically:

SELECT town, 
    STRING_AGG (email, ';') WITHIN GROUP (ORDER BY email ASC) AS emails 
FROM dbo.Employee 
GROUP BY town; 

以下為結果集:Here is the result set.

鄉鎮town 電子郵件emails
SeattleSeattle catherine0@adventure-works.com;kim2@adventure-works.com;syed0@adventure-works.comcatherine0@adventure-works.com;kim2@adventure-works.com;syed0@adventure-works.com
LALA hazem0@adventure-works.com;sam1@adventure-works.comhazem0@adventure-works.com;sam1@adventure-works.com

另請參閱See also

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