string_agg_SQL中STRING_AGG函数的概述

最新推荐文章于 2024-04-25 10:33:52 发布

culuo4781

最新推荐文章于 2024-04-25 10:33:52 发布

阅读量1.1w

点赞数 4

文章标签：字符串 java python 数据库 sql

原文链接：https://www.sqlshack.com/string_agg-function-in-sql/

版权

string_agg

In this article, we will explore the STRING_AGG function in SQL and we will also reinforce our learning with various examples. STRING_AGG function can be counted as a new member of the SQL aggregation functions and it was announced in SQL Server 2017. STRING_AGG function gathers all expression from rows and then puts it together in these rows expression in order to obtain a string. Additionally, this function takes a separator parameter that allows separating the expressions to be concatenated.

在本文中，我们将探索STRING_AGG SQL函数，我们还将通过各种示例来加强我们的学习。 STRING_AGG 该函数可以被视为SQL聚合函数的新成员，并且已在SQL Server 2017中宣布。STRING_AGG函数从行中收集所有表达式，然后将它们放到这些行表达式中以获得字符串。此外，此函数使用一个分隔符参数，该参数允许分隔要连接的表达式。

这个怎么运作 (How it works)

The following image illustrates the working mechanism of the STRING_AGG function. In this illustration, STRING_AGG function takes all rows expression from Column1 and then combines these expressions and it also adds the hyphen (–) sign as a separator between these expressions. The resulting output of the function will be If-you-want-a-happy-life-save-earth:

下图说明了STRING_AGG函数的工作机制。在此图例中，STRING_AGG函数从Column1中获取所有行表达式，然后组合这些表达式，并在这些表达式之间添加连字符（ - ）作为分隔符。该函数的最终输出将是“ 是否要过一个幸福的生活” ：

The working mechanism of the STRING_AGG function in SQL

STRING_AGG函数第一个示例 (STRING_AGG function first example)

Firstly, we will start a pretty simple example of STRING_AGG function and then we will examine the syntax and other details about this function. We will create a sample table and populate it with some synthetic data so that we can use this table whole examples of the article. The following script will help to generate a sample table called PersonTestTable:

首先，我们将开始一个非常简单的STRING_AGG函数示例，然后我们将检查该函数的语法和其他详细信息。我们将创建一个示例表，并用一些综合数据填充它，以便我们可以在本文的整个示例中使用此表。以下脚本将有助于生成一个名为PersonTestTable的示例表：

DROP TABLE IF EXISTS [PersonTestTable]
GO
CREATE TABLE [PersonTestTable](
    [FirstName] [varchar](400) NULL,
    [LastName] [varchar](400) NULL,
    [Mail] [varchar](100) NULL,
    Country [varchar](100) NULL,
    Age [int] NULL
    
) ON [PRIMARY]
GO
    
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Lawrence',N'Williams',N'uhynb.ndlguey@vtq.org',N'U.S.A.',21)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Gilbert',N'Miller',N'loiysr.jeoni@wptho.co',N'U.S.A.',53)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Salvador',N'Rodriguez',N'tjybsrvg.rswed@uan.org',N'Russia',46)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Ernest',N'Jones',N'psxkrzf.jgcmc@pfdknl.org',N'U.S.A.',48)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Jerome',N'Garcia',NULL,N'Russia',46)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Roland',N'Smith','xpdek.qpl@kpl.com',N'U.S.A. ',35)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Stella',N'Johnson',N'qllyoxgr.jsntdty@pzwm.org',N'Russia',24)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Aria',N'Anderson',N'sjgnz.voyyc@cvjg.com',N'Brazil ',25)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Edward',N'Martinez','pokjs.oas@mex.com',N'Mexico ',27)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Nicholas',N'Brown',N'wpfiki.hembt@uww.co',N'Russia ',43)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Ray',N'Wilson',NULL,N'Russia',41)
INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Jorge',N'Davis',N'bhlji.zwngl@kle.com',N'Russia ',49)
GO

In this first example, the STRING_AGG function will take all rows expression from the FirstName column of the PersonTestTable table and then generate a concatenated string with these rows expression. At the same time, the concatenated expressions will be separated with the hyphen (–) sign:

在第一个示例中，STRING_AGG函数将从PersonTestTable表的FirstName列中获取所有行表达式，然后使用这些行表达式生成一个串联字符串。同时，连接的表达式将用连字符（ – ）分隔：

SELECT FirstName FROM PersonTestTable
SELECT STRING_AGG(FirstName,'-') AS Result FROM PersonTestTable

STRING_AGG function example of result illustration

SQL中STRING_AGG函数的语法 (Syntax of STRING_AGG function in SQL)

The syntax of the STRING_AGG function looks like below:

STRING_AGG函数的语法如下所示：

STRING_AGG ( expression, separator ) [ &lt;order_clause&gt; ]

The expression parameter specifies any expressions that will be concatenated. The separator parameter is used to separate expressions that will be concatenated. The order_clause parameter is an optional parameter and helps to order the concatenated expression and it must be used with WITHIN GROUP statement.

expression参数指定将被连接的所有表达式。 分隔符参数用于分隔将被连接的表达式。 order_clause参数是一个可选参数，有助于排序连接的表达式，它必须与WITHIN GROUP语句一起使用。

SQL中STRING_AGG函数的排序结果 (Sorting result of STRING_AGG function in SQL)

STRING_AGG function allows sorting concatenated expressions in descending or ascending order. In this example, we will sort the concatenated expressions according to the FirstName column rows expression with the WITHIN GROUP statement:

STRING_AGG函数允许按降序或升序对连接的表达式进行排序。在此示例中，我们将使用WITHIN GROUP语句根据FirstName列的行表达式对连接的表达式进行排序：

SELECT FirstName FROM [PersonTestTable] ORDER BY FirstName ASC
GO
SELECT STRING_AGG(FirstName,'-')  WITHIN GROUP ( ORDER BY FirstName ASC)  AS Result FROM [PersonTestTable]

STRING_AGG function usage with WITHIN GROUP statement

As we can see clearly the STRING_AGG function sorted the concatenated expressions in the ascending order according to row values of the FirstName column. We need to underline one point about this type of usages. The GROUP BY clause will be necessary if the STRING_AGG result is not a sole column in the result set of the query. In the next section, we will learn this concept.

如我们所见，STRING_AGG函数根据FirstName的行值以升序对连接的表达式进行排序柱。我们需要强调关于这种用法的一点。的如果STRING_AGG结果不是查询结果集中的唯一列，则必须使用GROUP BY子句。在下一节中，我们将学习此概念。

如何使用STRING_AGG分组连接的表达式 (How to group concatenated expressions with STRING_AGG)

GROUP BY clause provides grouping the rows that have the same values in SQL Server. In the following example, we will generate grouped and concatenated e-mail addresses by the Country column:

GROUP BY子句提供对SQL Server中具有相同值的行进行分组的功能。在以下示例中，我们将通过“ 国家/地区”列生成分组和链接的电子邮件地址：

SELECT Country,STRING_AGG(Mail,',')  WITHIN GROUP ( ORDER BY FirstName ASC)  AS
Result FROM PersonTestTable
GROUP BY Country
ORDER BY Country asc

Grouping concatenated expressions with the STRING_AGG function in SQL

At this point, we have to take account of one consideration about the STRING_AGG function. The NULL values are ignored when the STRING_AGG concatenates the expressions in the rows and it also does not add an extra separator between the expressions due to NULL values. The following example will be illustrated in this case:

在这一点上，我们必须考虑到有关STRING_AGG函数的一项考虑。当STRING_AGG连接行中的表达式时，将忽略NULL值，并且由于NULL值，它也不会在表达式之间添加额外的分隔符。在这种情况下，将说明以下示例：

SELECT Country,Mail
Result FROM [PersonTestTable]
where country='Russia' 
group by Country, Mail
    
SELECT Country,
STRING_AGG(Mail,',')  WITHIN GROUP ( ORDER BY Mail ASC)  AS
Result FROM [PersonTestTable]
where country='Russia' 
group by Country

NULL value and STRING_AGG function interaction

As we can see, the NULL value did not affect the result of the function.

如我们所见，NULL值不影响函数的结果。

可以代替STRING_AGG函数使用的旧方法 (The old method that can be used instead of STRING_AGG function)

If we are using an older version than SQL Server 2017, we can use FOR XML PATH and STUFF statements combinations in the query to concatenate rows expressions. However, this method is more complicated than STRING_AGG function but it can be useful for the older version of the SQL Server. The following query returns the same result as the previous sample:

如果我们使用的版本比SQL Server 2017老，则可以在查询中使用FOR XML PATH和STUFF语句组合来连接行表达式。但是，此方法比STRING_AGG函数更复杂，但是对于旧版本SQL Server可能有用。以下查询返回与上一个示例相同的结果：

SELECT STUFF((SELECT '-' + FirstName as [text()] FROM PersonTestTable FOR XML PATH('')),1,1,'') AS Result

STUFF and FOR XML PATH usage for string concatenation

FOR XML PATH statement provides to generate an XML element from the query result. When we execute the following query, it transforms query result to XML:

FOR XML PATH语句提供了从查询结果生成XML元素的功能。当我们执行以下查询时，它将查询结果转换为XML：

SELECT '-' + FirstName as [text()] FROM PersonTestTable FOR XML PATH

If we click the result of the query, we can find out the XML more clearly:

如果单击查询结果，则可以更清楚地找到XML：

When we add the blank string option at the end of the FOR XML PATH, we will obtain concatenated and separated expressions:

当我们在FOR XML PATH的末尾添加空白字符串选项时，我们将获得串联和分隔的表达式：

SELECT '-' + FirstName as [text()] FROM PersonTestTable FOR XML PATH('')

STUFF function helps to delete a specified part of the string and then it can add a new string to it. Finally, we will clear the first extra separator with the STUFF function.

STUFF函数有助于删除字符串的指定部分，然后可以向其中添加新的字符串。最后，我们将使用STUFF函数清除第一个额外的分隔符。

如何在单个单元格中生成串联的行 (How to generate a concatenated rows in a single cell )

The carriage return allows setting the cursor to the beginning of the next line. We can provide this option in SQL with CHAR(13) statement. We will use CHAR(13) statement as a separator parameter for STRING_AGG function so that we can generate concatenated rows into a single cell:

回车允许将光标设置到下一行的开头。我们可以使用CHAR（13）语句在SQL中提供此选项。我们将使用CHAR（13）语句作为STRING_AGG函数的分隔符参数，以便我们可以将串联的行生成到单个单元格中：

SELECT STRING_AGG(FirstName,CHAR(13)) AS Result FROM [PersonTestTable]

Generating a concatenated row in a single cell

As we can see, we did not obtain the result that we wished in the SQL Server Management Studio result tab. In fact, it misleads us in SSMS because of the query result option. We will change this option in SSMS so that we achieve the proper visual. We will find the Options setting under the Tools menu and then change the Default destination for result option in the Query Results setting. We will change the Results to grids option to Result to text:

如我们所见，我们没有在SQL Server Management Studio的“结果”选项卡中获得所需的结果。实际上，由于查询结果选项，它在SSMS中误导了我们。我们将在SSMS中更改此选项，以便获得正确的视觉效果。我们将在“ 工具”菜单下找到“ 选项”设置，然后在“ 查询结果”设置中更改“ 结果的默认目标”选项。我们将“ 结果转换为网格”选项更改为“ 结果转换 为文本” ：

Change SSMS default destination for result

After this option changing, SSMS shows the result in the text. We will re-execute the same query in a new query window:

更改此选项后，SSMS将在文本中显示结果。我们将在新的查询窗口中重新执行相同的查询：

SELECT STRING_AGG(FirstName,CHAR(13)) AS Result FROM PersonTestTable

Generating a concatenated row in a single cell with STRING_AGG

如何在STRING_AGG函数中删除重复的值 (How to remove duplicate values in STRING_AGG function)

In some cases, we may need to eliminate duplicate values from the concatenated result of the STRING_AGG function. To handle this issue, we can use a two-tier query. In the first SELECT statement, we will eliminate the duplicate rows and then obtain unique values. Then, we will concatenate the unique expression with the STRING_AGG function:

在某些情况下，我们可能需要从STRING_AGG函数的串联结果中消除重复的值。要解决此问题，我们可以使用两层查询。在第一个SELECT语句中，我们将消除重复的行，然后获得唯一的值。然后，我们将用STRING_AGG函数连接唯一表达式：

SELECT STRING_AGG(Cnty, '-')  FROM
(
 (SELECT DISTINCT Country AS [Cnty] FROM PersonTestTable)
) AS TMP_TBL

Eliminating duplicates values in the STRING_AGG Function in SQL

有关SQL中STRING_AGG函数的高级详细信息 (Advanced details about STRING_AGG function in SQL)

The result type of STRING_AGG is determined according to the first expression that is taken by the function. There is no doubt that the nvarchar and varchar types concatenated results will be in the same type. However, if we concatenate other datatypes which can be converted into string datatypes (int, float, datetime and etc.). The result data types will be NVARCHAR(4000) for non-string data types. In the following example, we will create a TempTableForFunction table that has only a float data type column. We will try to combine these table expressions through the STRING_AGG function and then the function result will create the TempTableForFunctionResult table. TempTableForFunctionResult column will be generated according to STRING_AGG function result datatype:

根据该函数采用的第一个表达式来确定STRING_AGG的结果类型。毫无疑问， nvarchar和varchar类型的连接结果将是同一类型。但是，如果我们串联其他可以转换为字符串数据类型（int，float，datetime等）的数据类型。对于非字符串数据类型，结果数据类型将为NVARCHAR（4000）。在下面的示例中，我们将创建一个仅具有float数据类型列的TempTableForFunction表。我们将尝试通过STRING_AGG函数组合这些表表达式，然后函数结果将创建TempTableForFunctionResult表。 TempTableForFunctionResult列将根据STRING_AGG函数结果数据类型生成：

DROP TABLE IF EXISTS TempTableForFunction
DROP TABLE IF EXISTS TempTableForFunctionResult
GO
    
CREATE TABLE TempTableForFunction
(SampleVal Float)
    
INSERT INTO TempTableForFunction VALUES (12.67) , (98.09),(65.42),(56.72),(129.12)
    
SELECT STRING_AGG(SampleVal,'-') WITHIN GROUP ( ORDER BY SampleVal ASC) AS Result INTO  TempTableForFunctionResult   FROM TempTableForFunction  
    
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TempTableForFunctionResult'
    
SELECT STRING_AGG(SampleVal,'-') WITHIN GROUP ( ORDER BY SampleVal ASC) AS Result   FROM TempTableForFunction

Now, examine the result:

现在，检查结果：

Analyzing STRING_AGG function result data type

The following table shows the STRING_AGG function result types against expression data types:

下表显示了针对表达式数据类型的STRING_AGG函数结果类型：

Input expression type	STRING_AGG function result type
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函数结果类型
NVARCHAR（最大）	NVARCHAR（最大）
VARCHAR（最大）	VARCHAR（最大）
NVARCHAR（1…4000）	NVARCHAR（4000）
VARCHAR（1…8000）	VARCHAR（8000）
int，bigint，smallint，tinyint，数值，浮点数，实数，位，十进制，smallmoney，货币，datetime，datetime2，	NVARCHAR（4000）

At this point, we have to take account of one issue about the STRING_AGG function, it sorted the numbers properly. If we look behind at the scene of the query, it means that we will analyze the execution plan of the following query with ApexSQL Plan:

在这一点上，我们必须考虑到有关STRING_AGG函数的一个问题，它正确地对数字进行了排序。如果我们回头查看查询场景，则意味着我们将使用ApexSQL Plan分析以下查询的执行计划：

SELECT STRING_AGG(SampleVal,'-') WITHIN GROUP ( ORDER BY SampleVal ASC) AS Result FROM TempTableForFunction

STRING_AGG function execution plan of the query

The sort operation is processed before the Stream Aggregate operation so the numbers are sorted out properly. On the other hand, there is a warning sign shown over the SELECT image. If we hover over this image, we can find out more details about this issue:

排序操作在“流聚合”操作之前进行处理，因此编号已正确排序。另一方面，在SELECT图像上方显示警告标志。如果将鼠标悬停在此图像上，我们可以找到有关此问题的更多详细信息：

In the above image, the implicit conversion process is shown clearly. Implicit conversion occurs when the SQL Server query execution processes are required to convert one data type to another one and this process is automatically executed during the query execution. In addition, you can look at the Implicit conversion in SQL Server article to learn more details about the implicit conversion notion.

在上图中，清楚地显示了隐式转换过程。当需要SQL Server查询执行过程将一种数据类型转换为另一种数据类型，并且在查询执行期间自动执行此过程时，就会发生隐式转换。此外，您可以查看SQL Server中的隐式转换文章，以了解有关隐式转换概念的更多详细信息。

结论 (Conclusion)

In this article, we explored the STRING_AGG function in SQL and completed various examples of this function. STRING_AGG is a very useful and simple function to convert rows expression into a single string. On the other hand, we can use the older version methods to solve these types of issues.

在本文中，我们探索了SQL中的STRING_AGG函数，并完成了该函数的各种示例。 STRING_AGG是将行表达式转换为单个字符串的非常有用且简单的函数。另一方面，我们可以使用旧版本的方法来解决这些类型的问题。

翻译自: https://www.sqlshack.com/string_agg-function-in-sql/

string_agg

culuo4781

关注

4
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
string_agg_SQL中STRING_AGG函数的概述

string_agg In this article, we will explore the STRING_AGG function in SQL and we will also reinforce our learning with various examples. STRING_AGG function can be counted as a new member...
复制链接

扫一扫