use tempdb
if object_id('test','u') is not null drop table test
create table test(id int,string nvarchar(max))
insert into test(id,string)
select 1,'a,b' union all
select 1,'b,c' union all
select 1,'e,f' union all
select 2,'a' union all
select 2,'a,c' union all
select 2,'c,e,f' --需求:想创建一个自定义的聚合函数实现如下结果
select id,dbo.test(string) as string from test
group by id
------------------------
id string
1 a,b,c,e,f
2 a,c,e,f--PS: 只是求自定义聚合函数的实现,而非其它方式,谢谢。
但本质上个人觉得这个是伪聚合函数,换个表就不好用了
我本意想写出像系统聚合函数那样的,诸如max(),sum()...
use tempdb
if object_id('test','u') is not null drop table test
create table tb(id int,string nvarchar(max))
insert into tb(id,string)
select 1,'a,b' union all
select 1,'b,c' union all
select 1,'e,f' union all
select 2,'a' union all
select 2,'a,c' union all
select 2,'c,e,f' /*--需求:想创建一个自定义的聚合函数实现如下结果
select id,dbo.test(string) as string from test
group by id
------------------------
id string
1 a,b,c,e,f
2 a,c,e,f--PS: 只是求自定义聚合函数的实现,而非其它方式,谢谢。*/create function test(@str nvarchar(max))
returns @tb table (col varchar(20))
as
begin
set @str=@str+','
while charindex(',',@str)>0
begin
insert into @tb
select left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
end
return
endwith cte as
(
select distinct id, b.*
from tb a cross apply dbo.test(string) b
)select id,
string=stuff((select ','+col from cte where id=a.id for xml path('')),1,1,'')
from cte a group by id