想把上面的表合并成下面的表

sum 语文 数学 英语。。。
group by 学号的话


如果没有备注就没问题，有备注怎么办
这是情况1


情况2是，两行备注不一样，想合并后变成“备注1|备注2”这样，如何实现呢，谢谢先。

Oracle的话有个concate？函数可以合并备注Hi Google

谢谢，忘了说，是sql server

搜了一下，有个函数STUFF，不知道有没有更合适的

-- 准备数据
if exists (select * from dbo.sysobjects where id = object_id(N'[temptable-0F06000F-B5C1-433B-9E51-38C3CA1AA61A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.[temptable-0F06000F-B5C1-433B-9E51-38C3CA1AA61A]
GO
create table dbo.[temptable-0F06000F-B5C1-433B-9E51-38C3CA1AA61A](
xh varchar(20),
yw numeric(18,2),
sx numeric(18,2),
yy numeric(18,2),
wl numeric(18,2),
hx numeric(18,2),
bz varchar(100))

insert dbo.[temptable-0F06000F-B5C1-433B-9E51-38C3CA1AA61A] values('1001',100,null,null,50,100,'备注1')
insert dbo.[temptable-0F06000F-B5C1-433B-9E51-38C3CA1AA61A] values('1001',null,99,100,60,null,'备注2')
go




-- 建立一个函数采集备注
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getbz-0F06000F-B5C1-433B-9E51-38C3CA1AA61A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getbz-0F06000F-B5C1-433B-9E51-38C3CA1AA61A]
GO

create Function dbo.[getbz-0F06000F-B5C1-433B-9E51-38C3CA1AA61A](@xh varchar(20))
returns varchar(4000)
as
begin

declare @bz varchar(4000)
set @bz=''
select @bz=@bz+'|'+bz from dbo.[temptable-0F06000F-B5C1-433B-9E51-38C3CA1AA61A] where xh=@xh and isnull(bz,'')<>'' order by bz
select @bz=ltrim(STUFF(@bz,1,1,''))

return @bz
end
go


-- 利用函数整理备注，成绩直接求和
select xh,yw=sum(yw),sx=sum(sx),yy=sum(yy),wl=sum(wl),hx=sum(hx),
bz=max(dbo.[getbz-0F06000F-B5C1-433B-9E51-38C3CA1AA61A](xh))
from dbo.[temptable-0F06000F-B5C1-433B-9E51-38C3CA1AA61A] group by xh
go




-- 删除临时表和临时函数
if exists (select * from dbo.sysobjects where id = object_id(N'[temptable-0F06000F-B5C1-433B-9E51-38C3CA1AA61A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.[temptable-0F06000F-B5C1-433B-9E51-38C3CA1AA61A]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getbz-0F06000F-B5C1-433B-9E51-38C3CA1AA61A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getbz-0F06000F-B5C1-433B-9E51-38C3CA1AA61A]
GO

查询结果：

看来就是stuff，谢谢了HiPDA·NG