求一種這樣的sql寫法

[zhuqiang9]

id why
1 a
1 b
1 c
2 d
2 b

----result
id why
1 a+b+c
2 d+b


這種類型的sql，誰可以幫助我實現謝謝

[冷箫轻笛]

--创建用户定义函数
create function f_str(@id int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+'+'+why from 表 where id = @id
set @ret = stuff(@ret,1,1,'')
return @ret
end
go


--执行
select id,why=dbo.f_str(id) from 表 group by id order by id
go

[caixia615]

create table tb
(
id int,
why char(10)
)
insert into tb(id,why) values(1,'a')
insert into tb(id,why) values(1,'b')
insert into tb(id,why) values(1,'c')
insert into tb(id,why) values(2,'d')
insert into tb(id,why) values(2,'b')

go

--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '+' + why from tb where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go

--调用自定义函数得到结果：
select distinct id,why=dbo.f_hb(id) from tb

drop table tb

[marco08]

create table T(id int, why char(1))
insert T select 1, 'a'
union all select 1, 'b'
union all select 1, 'c'
union all select 2, 'd'
union all select 2, 'b'

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

return @re
end

select id, dbo.fun(id) as Why from T group by id

--result
id Why
----------- -------------------
1 a+b+c
2 d+b

(2 row(s) affected)