表tb结构如下
a b
-----------------
kai abc
kai def
kai ghi
jim xxx
tom yyy
要求得到将a字段重复的多行,合并成一行,b字段用逗号分隔a b
-----------------
kai abc,def,ghi
jim xxx
tom yyy
a b
-----------------
kai abc
kai def
kai ghi
jim xxx
tom yyy
要求得到将a字段重复的多行,合并成一行,b字段用逗号分隔a b
-----------------
kai abc,def,ghi
jim xxx
tom yyy
(
a varchar(10),
b varchar(10)
)insert A select 'kai','abc'
insert A select 'kai','def'
insert A select 'kai','ghi'
insert A select 'jim','xxx'
insert A select 'tom','yyy'---建立函数create Function T_Test(@a varchar(10))
returns varchar(800)
as
begin
declare @T varchar(800)
set @T=''
select @T=@T + b +',' from A where a=@a
set @T=left(@T,len(@T)-1)
return @T
endselect a,dbo.T_Test(a) from A group by a
(
a varchar(10),
b varchar(10)
)insert A select 'kai','abc'
insert A select 'kai','def'
insert A select 'kai','ghi'
insert A select 'jim','xxx'
insert A select 'tom','yyy'create Function dbo.f_A(@col varchar(10))
returns varchar(100)
as
begin
declare @re varchar(100)
set @re=''
select @re=@re+','+cast(b as varchar)
from A
where a=@col
return(stuff(@re,1,1,''))
end
goselect a,b=dbo.f_A(a) from a group by a