【转】Sql server 2005 中的PIVOT和UNPIVOT中的应用

原文链接：http://blog.csdn.net/zlp321002/archive/2007/04/02/1549492.aspx

现有一张表（没有现成的表，临时产生）格式如下：
         机器1  机器2  机器3  机器4  机器5  机器6
组别1       1      2      3       4       5      6
组别2       5      9      7       2       1      3
组别3       9      5      6       4       4      3
现在想转换成如下格式：
       组别1  组别2  组别3
机器1   1       5      9
机器2   2       9      5
机器3   3       7      6
机器4   4       2      4
机器5   5       1      4
机器6   6       3      5
不知道怎么实现？请高手指教！谢谢！
能解释sql语句的含义最好！！

 

--测试环境
declare @t table(zb varchar(10),机器1 int,机器2 int,机器3 int,机器4 int,机器5 int,机器6 int)
insert into @t select '组别1',1,2,3,4,5,6
union all select '组别2',5,9,7,2,1,3
union all select '组别3',9,5,6,4,4,3

SELECT * FROM 
(
SELECT  Employee,zb,Orders
FROM 
   (SELECT zb, 机器1, 机器2, 机器3, 机器4, 机器5, 机器6
   FROM @t) p
UNPIVOT
   (Orders FOR Employee IN 
      (机器1, 机器2, 机器3, 机器4, 机器5, 机器6)
)AS unpvt
) T
 PIVOT
(MAX(ORDERS)
 for zb in ([组别1],[组别2],[组别3])
)as pt
--结果
/**//*
Employee                                                                                                                         组别1         组别2         组别3
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -----------
机器1                                                                                                                              1           5           9
机器2                                                                                                                              2           9           5
机器3                                                                                                                              3           7           6
机器4                                                                                                                              4           2           4
机器5                                                                                                                              5           1           4
机器6                                                                                                                              6           3           3

(6 行受影响)
*/