行列置换(2000中的交叉表.2005中的处理方法.及相关合计字段列的处理.)
declare @t table(name varchar(10),object varchar(10),score int)
insert into @t select 'a','EN',89
union all select 'a','CH',78
union all select 'a','HO',99
union all select 'b','EN',34
union all select 'b','CH',88
union all select 'b','HO',66

select ta.*,tb.[sum] from
(
select * from @t
pivot
(sum(score)
for object in ([EN],[CH],[HO])
)
as pt
)ta left join
(
select name,[sum]=sum(score)
from @t
group by name
)tb
on ta.name=tb.name


/**//*
name EN CH HO sum
---------- ----------- ----------- ----------- -----------
a 89 78 99 266
b 34 88 66 188

(2 行受影响)
*/