create table tt(id int,name varchar(20))
insert into tt
select 1,'aa'
union all select 1,'bb'
union all select 1,'cc'
union all select 2,'dd'
union all select 3,'ee'
union all select 3,'ff'
go
create function f_str(@id varchar(4))
returns varchar(100)
as
begin
declare @str varchar(100)
set @str = ''
select @str = @str+','+cast(name as varchar(10)) from tt where id=@id
return stuff(@str,1,1,'')
end
select distinct id,dbo.f_str(id) name from tt
/*
id,name
--------
1 aa,bb,cc
2 dd
3 ee,ff
*/
drop table tt
drop function f_str
