create table T(ID int, CharCol char(1)) insert T select 1, 'A' union all select 1, 'B' union all select 1, 'C' union all select 2, 'B' union all select 2, 'D'create function fun(@ID int) returns varchar(100) as begin declare @re varchar(100) set @re='' select @re=@re+CharCol+',' from T where ID=@ID select @re=left(@re, len(@re)-1) return @re endselect distinct ID, dbo.fun(ID) as CharCol_Total from T --result ID CharCol_Total ----------- -------------------- 1 A,B,C 2 B,D(2 row(s) affected)
SQL Server2005可以用一句SQL搞掂
create function f_getstr(@id varchar(64)) RETURNS varchar(8000) as begin declare @s varhcar(8000) set @s = '' select @s = @s+','+CharCol from tb where id =@id return stuff(@s,1,1,'') end
insert T select 1, 'A'
union all select 1, 'B'
union all select 1, 'C'
union all select 2, 'B'
union all select 2, 'D'create function fun(@ID int)
returns varchar(100)
as
begin
declare @re varchar(100)
set @re=''
select @re=@re+CharCol+',' from T where ID=@ID
select @re=left(@re, len(@re)-1) return @re
endselect distinct ID, dbo.fun(ID) as CharCol_Total from T
--result
ID CharCol_Total
----------- --------------------
1 A,B,C
2 B,D(2 row(s) affected)
RETURNS varchar(8000)
as
begin
declare @s varhcar(8000)
set @s = ''
select @s = @s+','+CharCol from tb where id =@id
return stuff(@s,1,1,'')
end