create function f_str(@id int) returns varchar(8000) as begin declare @ret varchar(8000) set @ret ='' select @ret =@ret+'+'+ name from tableA where class=@id set @ret=stuff(@ret,1,1,'') return @ret end gocreate table tableA(class int, name Varchar(10)) insert tableA select 1,'A' union all select 1,'B' union all select 1,'C' union all select 2,'D' union all select 2,'E' union all select 3,'F'select distinct name=dbo.f_str(class)from tableA drop table tableA drop function f_strname ------------------------------------------------------------------------------------- A+B+C D+E F(3 row(s) affected)
没办法,有这个业务需求~唉~努力想想,帮帮忙~
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret =''
select @ret =@ret+'+'+ name from tableA where class=@id
set @ret=stuff(@ret,1,1,'')
return @ret
end
gocreate table tableA(class int, name Varchar(10))
insert tableA select 1,'A'
union all select 1,'B'
union all select 1,'C'
union all select 2,'D'
union all select 2,'E'
union all select 3,'F'select distinct name=dbo.f_str(class)from tableA
drop table tableA
drop function f_strname
-------------------------------------------------------------------------------------
A+B+C
D+E
F(3 row(s) affected)