SQL2005 新功能:行列轉換

--Create the table and insert values as portrayed in the above example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
VendorID    Employee                                                                                                                         Orders
----------- -------------------------------------------------------------------------------------------------------------------------------- -----------
1           Emp1                                                                                                                             4
1           Emp2                                                                                                                             3
1           Emp3                                                                                                                             5
1           Emp4                                                                                                                             4
1           Emp5                                                                                                                             4
2           Emp1                                                                                                                             4
2           Emp2                                                                                                                             1
2           Emp3                                                                                                                             5
2           Emp4                                                                                                                             5
2           Emp5                                                                                                                             5
3           Emp1                                                                                                                             4
3           Emp2                                                                                                                             3
3           Emp3                                                                                                                             5
3           Emp4                                                                                                                             4
3           Emp5                                                                                                                             4
4           Emp1                                                                                                                             4
4           Emp2                                                                                                                             2
4           Emp3                                                                                                                             5
4           Emp4                                                                                                                             5
4           Emp5                                                                                                                             4
5           Emp1                                                                                                                             5
5           Emp2                                                                                                                             1
5           Emp3                                                                                                                             5
5           Emp4                                                                                                                             5
5           Emp5                                                                                                                             5

(25 row(s) affected)

SELECT VendorID, Employee, Orders
 into pvt_cc FROM
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
select * from pvt_cc
pivot (
sum(Orders)
for Employee in (Emp1, Emp2, Emp3, Emp4, Emp5)
) as pivt

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
----------- ----------- ----------- ----------- ----------- -----------
1           4           3           5           4           4
2           4           1           5           5           5
3           4           3           5           4           4
4           4           2           5           5           4
5           5           1           5           5           5

(5 row(s) affected)