SQL Server 2005 T-SQL学习笔记：PIVOT和UNPIVOT

概念：PIVOT提供将行转换了列的功能，UNPIVOT提供相反的功能．

PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合

用处：交叉报表

基本用法：

 

SELECT   < non - pivoted  column >  ,

     [ first pivoted column ]   AS   < column  name >  ,

     [ second pivoted column ]   AS   < column  name >  ,

    

     [ last pivoted column ]   AS   < column  name >

FROM

    (  < SELECT  query that produces the data >  ) 

     AS   < alias  for  the source query >

PIVOT

(

     < aggregation  function > (  < column  being aggregated >  )

FOR  

[ <column that contains the values that will become column headers> ]  

     IN  (  [ first pivoted column ]  ,  [ second pivoted column ]  ,

      [ last pivoted column ]  )

)  AS   < alias  for  the pivot  table >

< optional  ORDER   BY  clause >

 

示例一（查询原始一个两列四行的表）：

 

USE  AdventureWorks ;
GO
SELECT  DaysToManufacture,  AVG (StandardCost)  AS  AverageCost 
FROM  Production.Product
GROUP   BY  DaysToManufacture

 

示例二（转换行列）：

 

select   ' AverageCost '   as  AverageCost, *   from
(
     select  DaysToManufacture, StandardCost
     from  Production.Product
)  as  piv PIVOT
(
     avg (StandardCost)
     for  DaysToManufacture  in  ( [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )
)  as  PivotTable

 

示例三（查询指定几位员工在各个厂商中的订单数量）：

 

USE  AdventureWorks;
GO
SELECT  VendorID,  [ 164 ]   AS  Emp1,  [ 198 ]   AS  Emp2,  [ 223 ]   AS  Emp3,  [ 231 ]   AS  Emp4,  [ 233 ]   AS  Emp5
FROM  
( SELECT  PurchaseOrderID, EmployeeID, VendorID
FROM  Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT  (PurchaseOrderID)
FOR  EmployeeID  IN
(  [ 164 ] ,  [ 198 ] ,  [ 223 ] ,  [ 231 ] ,  [ 233 ]  )
)  AS  pvt
ORDER   BY  VendorID

结果
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

 

UNPIVOT 将与 PIVOT 执行几乎完全相反的操作，将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt，并且您需要将列标识符 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋转为对应于特定供应商的行值。这意味着必须标识另外两个列。包含要旋转的列值（Emp1、Emp2...）的列将被称为 Employee，将保存当前位于待旋转列下的值的列被称为 Orders。这些列分别对应于 Transact-SQL 定义中的 pivot_column 和 value_column。以下为该查询。

 示例四（UNPIVOT）：

 

-- Create the table and insert values as portrayed in the previous 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
-- Unpivot the table.
SELECT  VendorID, Employee, Orders
FROM  
   ( SELECT  VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM  pvt) p
UNPIVOT
   (Orders  FOR  Employee  IN  
      (Emp1, Emp2, Emp3, Emp4, Emp5)
) AS  unpvt
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
...