使用辅助函数合并字符串,这里有很多啊。
create function fns_GetStr(@a int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
select @re=''
select @re=@re+','+b from 表A where a=@a group by a
select @re=stuff(@re,1,1,'')
return @re
end
--查询
select a,dbo.fns_getStr(a)
from 表A
group by a
create function fns_GetStr(@a int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
select @re=''
select @re=@re+','+b from 表A where a=@a group by a
select @re=stuff(@re,1,1,'')
return @re
end
--查询
select a,dbo.fns_getStr(a)
from 表A
group by a
create table 表A(a int,b varchar(20))
insert into 表A
select 1,'aa' union
select 1,'bb' union
select 1,'cc' union
select 2,'aa' union
select 2,'bb' union
select 3,'aa' union
select 3,'cc'--辅助函数
create function fns_GetStr(@a int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
select @re=''
select @re=@re+','+b from 表A where a=@a
select @re=stuff(@re,1,1,'')
return @re
end
--查询
select a,dbo.fns_getStr(a)
from 表A
group by a
/* 结果
1 aa,bb,cc
2 aa,bb
3 aa,cc*/