mssql sqlserver string_AGG字符串组合关键简介说明


摘要:
下文讲述一个多行字符串拼接函数”string_agg”的用法分享,如下所示:
实验环境:sql server 2017



string_agg语法简介:

STRING_AGG ( expression, separator ) [ ]
::=
WITHIN GROUP ( ORDER BY [ ASC | DESC ] )
参数说明:
expression:
参数表达式,这里需输入nvarchar或varchar类型
separator:
分隔符,用于列与列之间的分隔
WITHIN GROUP ( ORDER BY [ ASC | DESC ] ):
结果群组或排序所用字段


string_agg举例说明:

 

create table [maomao365] ( 
id int identity(1,1) primary key, 
sort varchar(88), 
info nvarchar(70) 
) 
insert into [maomao365] select 'A','sql教程' 
insert into [maomao365] select 'A','sql博客' 
insert into [maomao365] select 'B','sql测试信息' 
insert into [maomao365] select 'B','sql最新技巧站' 
insert into [maomao365] select 'C','sql报表'  
go

---string_agg关键字用法1
select string_agg(info,',') as [新组合字段]  from [maomao365]
 ----输出-----
'sql教程,sql博客...'

---string_agg关键字用法2
select A,string_agg(info,',') as [新组合字段]  from [maomao365]  group by A 
  
'A',''sql教程,sql博客'
'B''sql测试信息,sql最新技巧站'
'C','sql报表'