字符串组合函数的功能为:
将数据表中数据行,组合成一个字符串。
例:
表A
———————————-
keyId info
1 a
1 b
2 c
2 d
3 e
可组合为以下单个字符
组合列
a,b,c,d,e
或按条件组合
a,b
c,d,e
下文将展示字符串组合的方法
方式一:采用变量对字符串进行合并:
/*建表及生成表数据*/ create table A(keyId int,info varchar(30)) insert into A(keyId,info) values(1,'a'),(1,'b'),(2,'c'),(2,'d'),(2,'e'),(2,'f'),(2,'g') go declare @infoAll varchar(3000) set @infoAll ='' select @infoAll =@infoAll+info from A print @infoAll go truncate table A drop table A go
/*建表及生成表数据*/ create table A(keyId int,info varchar(30)) insert into A(keyId,info) values(1,'a'),(1,'b'),(2,'c'),(2,'d'),(2,'e'),(2,'f'),(2,'g') go /*创建字符串组合函数*/ create function dbo.str_join(@keyId int) returns varchar(100) as begin declare @infoAll varchar(3000) set @infoAll ='' select @infoAll =@infoAll+','+info from A(nolocK) where keyId=@keyId return @infoAll end go /*通过字符串组合函数组合数据1*/ select keyId,dbo.str_join(keyId) from A /*通过字符串组合函数组合数据2*/ select keyId,dbo.str_join(keyId) from A group by keyId go drop function dbo.str_join go truncate table A drop table A go
方式二 将指定行 采用 xml path直接生成数据
/*建表及生成表数据*/ create table A(keyId int,info varchar(30)) insert into A(keyId,info) values(1,'a'),(1,'b'),(2,'c'),(2,'d'),(2,'e'),(2,'f'),(2,'g') go /*通过for xml path组合数据1*/ select keyId, ( select ',' + info from A for xml path('') ) as zuhe from A /*通过for xml path组合数据1*/ select A2.keyId, ( select ',' + info from A where A.keyID=A2.keyId for xml path('') ) as zuhe from A as A2 go truncate table A drop table A go