一个关于合并结果的sql语句
create table #a(id varchar(50),cert varchar(50))
insert into #a (0, 'abc ')
insert into #a (0, 'def ')
insert into #a (0, 'gkh ')
insert into #a (1, 'abc ')
insert into #a (1, 'mn ')
insert into #a (2, 'abc ')

i want to get the result like follow:
result:
0,( 'abc ', 'def ',gkh)
1,( 'abc ', 'mn ')
2,( 'abc ')

help me. thanks




[解决办法]
create function fun_test(@cid varchar(2000))
returns varchar(2000)
as
begin
declare @chr varchar(2000)
set @chr= '( '
select @chr=@chr+c+ ', ' from #a where id=@cid
set @chr=@chr+ ') '
return @chr
end

select *,dbo.fun_test(id)from #a
[解决办法]

create table #a(id varchar(50),cert varchar(50))
insert into #a values( '0 ', 'abc ')
insert into #a values( '0 ', 'def ')
insert into #a values( '0 ', 'gkh ')
insert into #a values( '1 ', 'abc ')
insert into #a values( '1 ', 'mn ')
insert into #a values( '2 ', 'abc ')


select * into t from #a

create function dbo.aaa(@id varchar(1000))
returns varchar(100)
as
begin
declare @s varchar(100)
set @s= ' '
select @s=@s + ', '+cert from t where id=@id
return(stuff(@s,1,1, ' '))
end

select id, dbo.aaa(id) as name from t group by id


id name
-------------------------------------------------- ----------------------------------------------------------------
0 abc,def,gkh
1 abc,mn
2 abc

(3 row(s) affected)