能否将查询结果拼接一下,然后只返回一条记录

[zwsilence]

假如表中有如下内容:
id name
------------
1 zhang
1 li
1 wang

那么select name from table where id=1 会返回3条记录,如果我只想返回一条,并且name的值是以","隔开,即返回(zhang,li,wang),是否可行?

[冷箫轻笛]

declare @str varchar(50)
set @str = ''

select @str = @str + ',' + name from table where id = 1
set @str = stuff(@str,1,1,'')
select @str

[playwarcraft]

create function fn_str(@id int)
returns varchar(200)
as
begin
declare @str varchar(200)
set @str=''
select @str=@str+','+[name] from T where id=@id
set @str=stuff(@str,1,1,'')
return @str
end

Go

select id,dbo.fn_str(id) as [name]
from T
group by id

[caixia615]

create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str +','+ name from tablename where id= @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct id ,dbo.f_hb(id) as b from tablename

[marco08]

create table T (id int, name varchar(10))
insert T select 1, 'zhang'
union all select 1, 'li'
union all select 1, 'wang'

create function fun(@id int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+name+',' from T where id=@id

set @re=left(@re, len(@re)-1)
return @re
end

select distinct id, dbo.fun(id) as name from T

id name
----------- ----------
1 zhang,li,wang

(1 row(s) affected)

[zwsilence]

感谢大家的热心帮忙!