sql server 2005 中的列置换函数用法(pivot 字段)

行列置换(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 行受影响)
*/