group by分类汇总时，多行记录怎么进行拼接？
如数据库记录如下：
id val date
1 a 2008-1-1
1 b 2008-1-2
2 c 2008-1-3
2 d 2008-1-4
1 e 2008-1-5
通过id字段汇总时，把val字段拼接起来
结果是：
id val
1 a,b,e
2 c,d
请问上述目的怎么实现？
[解决办法]
--建立测试环境
Create Table 表(id varchar(10),amount integer,remark varchar(10))
--插入数据
insert into 表
select '1','3','aaa' union
select '1','5','bbb' union
select '1','4','ccc' union
select '2','10','pkoge' union
select '2','12','daf'
go
--测试语句
CREATE FUNCTION FunMergeCharField(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+remark FROM 表 WHERE id=@vchA
RETURN(substring(@r,2,8000))
END
GO
select id,sum(amount) as sum,dbo.FunMergeCharField(id) as remark叠加 from 表 group by id
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField
create table tb(id int, val varchar(10), date datetime)
insert into tb
select 1, 'a', '2008-1-1' union all
select 1, 'b', '2008-1-2' union all
select 2, 'c', '2008-1-3' union all
select 2, 'd', '2008-1-4' union all
select 1, 'e', '2008-1-5'
select * from tb
go
create function f_str(@id int)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+val from tb where id=@id
set @str=stuff(@str,1,1,'')
return (@str)
end
go
select id,dbo.f_str(id) from tb group by id
/*
id
--------------
1a,b,e
2c,d
*/
drop function dbo.f_str
drop table tb
