SQLServer STRING_AGG函数

SQLServer STRING_AGG函数

适用于：SQL Server 2017 (14.x) 及更高版本，参考官方文档

STRING_AGG (Transact-SQL) - SQL Server | Microsoft Learn

Azure SQL 数据库

Azure SQL 托管实例

Azure Synapse Analytics

Microsoft Fabric 中的 SQL 分析终结点

Microsoft Fabric 中的仓库

1、STRING_AGG 

语法

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 类型的表达式，用作串联字符串的分隔符。 可以是文本或变量。
• 使用 WITHIN GROUP 子句有选择性地指定串联结果的顺序：

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

• 可用于对结果进行排序的一系列非常量表达式。 每个查询只允许使用一个 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 是一个聚合函数，用于提取行中的所有表达式，并将这些表达式串联成一个字符串。 表达式值隐式转换为字符串类型，然后串联在一起。 隐式转换为字符串的过程遵循现有的数据类型转换规则。 有关数据类型转换的详细信息，请参阅 CAST 和 CONVERT (Transact-SQL)。

如果输入表达式的类型为 VARCHAR，则分隔符的类型不能是 NVARCHAR。

null 值会被忽略，且不会添加相应的分隔符。 若要为 null 值返回占位符，请使用 ISNULL 函数。

2、创建测试表及结果展示

create table t_city_list(id bigint identity(1,1) primary key,country varchar(32),city varchar(32),sysguid varchar(36));

alter table t_city_list add constraint df_t_city_list_uuid default newid() for sysguid;

insert into t_city_list(country,city) values('中国','上海');
insert into t_city_list(country,city) values('中国','北京');
insert into t_city_list(country,city) values('中国','深圳');
insert into t_city_list(country,city) values('美国','纽约');
insert into t_city_list(country,city) values('德国','柏林');
insert into t_city_list(country,city) values('日本','东京');
insert into t_city_list(country,city) values('美国','洛杉矶');
insert into t_city_list(country,city) values('日本','大阪');
insert into t_city_list(country,city) values('德国','慕尼黑');
insert into t_city_list(country,city) values('中国','广州');
insert into t_city_list(country,city) values('德国','汉堡');
insert into t_city_list(country,city) values('中国','兰州');
insert into t_city_list(country,city) values('德国','法兰克福');
insert into t_city_list(country,city) values('中国','中山');

select * from t_city_list;

id   country     city                               sysguid
---------------- -------------- ------------------------------------
1    中国          上海          4C4C860D-43D6-4A2F-8F00-06145309F030
2    中国          北京          2C4CCCFE-3C04-41B0-9032-622D4C62AD3B
3    中国          深圳          9C46FD2F-7DF8-4963-8FCD-9CBC8FA94B03
4    美国          纽约          B391521B-188A-4918-9D86-A31001475948
5    德国          柏林          FC485603-F83C-40D9-93E4-922CCF7DD044
6    日本          东京          628E696D-923E-46BC-BCAE-86555C1ADECC
7    美国          洛杉矶        95B6B1AB-7822-4EA0-9778-52172BC49F68
8    日本          大阪          4D8D2A45-0193-485E-A956-C1A0356E5BEC
9    德国          慕尼黑        66F4B79F-04BC-46B0-B9B6-9F7F56FEA7CA
10   中国          广州          D9B872E2-E72B-40B8-A9B5-584172348757
11   德国          汉堡          CB7857B5-83FD-449E-A4CE-FA7BF63BB376
12   中国          兰州          9FED1F97-C6EF-43FC-8277-88540231DD5B
13   德国          法兰克福      89DEFFFE-9340-4002-905E-30202C1768CC
14   中国          中山          C21FEF6D-593A-406C-BEDD-6C83CC04D9D4

(14 行受影响)

--  within group 指定id以升序排序列
select country,string_agg(city,'-') within group(order by id)  as stringagg_value
from t_city_list 
group by country;

country                          stringagg_value
-------------------------------- -------------------------------------------------------------------
德国                               柏林-慕尼黑-汉堡-法兰克福
美国                               纽约-洛杉矶
日本                               东京-大阪
中国                               上海-北京-深圳-广州-兰州-中山

-- within group 指定id以倒序序排序列
select country,string_agg(city,'-') within group(order by id desc) as stringagg_value
from t_city_list
group by country;

country                          stringagg_value
-------------------------------- ------------------------------------
德国                               法兰克福-汉堡-慕尼黑-柏林
美国                               洛杉矶-纽约
日本                               大阪-东京
中国                               中山-兰州-广州-深圳-北京-上海

3、SQL Server 2016 及更早版本实现类似功能

STUFF ( character_expression , start , length , replace_with_expression )

请参考官方文档，地址如下

STUFF (Transact-SQL) - SQL Server | Microsoft Learn

select
    country,
    stringagg_value = (STUFF((select ',' + city from t_city_list where country = A.country for xml path('')),1,1,''))
from t_city_list A
group by country
go

country                          stringagg_value
-------------------------------- ------------------------------------------
德国                               柏林,慕尼黑,汉堡,法兰克福
美国                               纽约,洛杉矶
日本                               东京,大阪
中国                               上海,北京,深圳,广州,兰州,中山