SQL语句如何写？

[coresky]

假设表结构为id，amount，remark。
remark为备注。如何实现下列的效果
select id,sum(amount) as sum,remark叠加 from tablename
group by id order by id。
其中remark叠加 要求相同id的remark以某种格式连接起来。

[xluzhong]

create function f_getrm(@id int)
returns nvarchar(1000)
as
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+remark from tablename where id=@id
return(stuff(@s,1,1,''))
go

select id,
sum(amount) as sum,
dbo.getrm(id) as remark
group by id
order by id

[jinjazz]

--建立测试环境
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