多行变一行的问题

[ask999]

原来的表如下：
1 a
1 b
1 c
2 d
2 e
2 f
现在查询后希望变为
1 a,b,c
2 d,e,f
用ADO+vb+sql2000

[vbman2003]

用存储过解决，SQL语句没法弄

[ask999]

存储过程怎么弄？分不够再加100哦。

[dlpseeyou]

create Function Gets_sh(@id as varchar(30))
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)

Set @S=''
Select @S=@S+','+ sh from table where id=@id
Return (Stuff(@S,1,1,''))
End

--select gets_sh(id) from table

[lianzy]

create table tb(id int,name varchar(100))
insert into tb(id,name) values (1,'a')
insert into tb(id,name) values (1,'b')
insert into tb(id,name) values (1,'c')
insert into tb(id,name) values (2,'d')
insert into tb(id,name) values (2,'e')
insert into tb(id,name) values (2,'f')

create function getStr(@id as int)
returns varchar(4000)
as
begin
	declare @s as varchar(4000)
	set @s=''
	select @s=@s+ ',' + name from tb where id=@id
	return stuff(@s,1,1,'')
end

select id,dbo.getStr(id) as name from tb group by id