日期:2014-05-19  浏览次数:20400 次

一个关于合并结果的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)