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
...