SQL使用 PIVOT 和 UNPIVOT
可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。
| 注意 |
| 对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。 |
PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。有关 PIVOT 语法的完整说明，请参阅 FROM (Transact-SQL)。
以下是带批注的 PIVOT 语法。
SELECT <非透视的列>,
[第一个透视的列] AS <列名称>,
[第二个透视的列] AS <列名称>,
...
[最后一个透视的列] AS <列名称>,
FROM
(<生成数据的 SELECT 查询>)
AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ( [第一个透视的列], [第二个透视的列],
... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;
简单 PIVOT 示例
下面的代码示例生成一个两列四行的表。
复制
USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
下面是结果集：
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
没有定义 DaysToManufacture 为 3 的产品。
以下代码显示相同的结果，该结果经过透视以使 DaysToManufacture 值成为列标题。提供一个列表示三 [3] 天，即使结果为 NULL。
复制
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
下面是结果集：
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
复杂 PIVOT 示例
可能会用到 PIVOT 的常见情况是：需要生成交叉表格报表以汇总数据。例如，假设需要在 AdventureWorks2008R2 示例数据库中查询 PurchaseOrderHeader 表以确定由某些特定雇员所下的采购订单数。以下查询提供了此报表（按供应商排序）。
复制
USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
以下为部分结果集。
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1492 2 5 4 4 4
1494 2 5 4 5 4
1496 2 4 4 5 5
1498 2 5 4 4 4
1500 3 4 4 5 4
将在 EmployeeID 列上透视此嵌套 select 语句返回的结果。
复制
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;
这意味着 EmployeeID 列返回的唯一值自行变成了最终结果集中的字段。因此，在透视子句中指定的每个 EmployeeID 号都有相应的一列：在本例中为雇员 164、198、223、231 和 233。PurchaseOrderID 列作为值列，将根据此列对最终输出中返回的列（称为分组列）进行分组。在本例中，通过 COUNT 函数聚合分组列。请注意，将显示一条警告消息，指出为每个雇员计算 COUNT 时未考虑显示在 PurchaseOrderID 列中的任何空值。
| 重要提示 |
| 如果聚合函数与 PIVOT 一起使用，则计算聚合时将不考虑出现在值列中的任何空值。 |
UNPIVOT 将与 PIVOT 执行几乎完全相反的操作，将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt，并且您需要将列标识符 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋转为对应于特定供应商的行值。这意味着必须标识另外两个列。包含要旋转的列值（Emp1、Emp2...）的列将被称为 Employee，将保存当前位于待旋转列下的值的列被称为 Orders。这些列分别对应于 Transact-SQL 定义中的 pivot_column 和 value_column。以下为该查询。
复制
--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
...
请注意，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合，从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果，因为行已经被合并了。另外，UNPIVOT 的输入中的空值不会显示在输出中，而在执行 PIVOT 操作之前，输入中可能有原始的空值。
来自 <https://technet.microsoft.com/zh-cn/library/ms177410.aspx>
SELECT TOP 1000000 AA.CT_MONTH
, BB.OPRID
, AA.EMPLID
, AA.FIELDNAME
, AA.COMMENT1
, CC.SORT_FIELD
FROM ( SELECT B.CT_MONTH , A.EMPLID , A.FIELDNAME , CONVERT(NVARCHAR(254) , CONVERT(DECIMAL(18,2) ,A.AMOUNT) ) AS COMMENT1 FROM (
SELECT EMPLID , CAL_RUN_ID , FIELDNAME , AMOUNT
FROM PS_CT_PAYROLL_WA UNPIVOT ( AMOUNT FOR FIELDNAME IN ( [B_ER_BASE] , [B_ATTEND] , [B_ER_OT1] , [B_ER_OT3] , [B_ER_OT] , [B_ER_SHIFT]
,[B_ER_BONM] ,[B_ER_ATTEND] ,[B_ER_HOT] ,[B_ER_BONS] ,[B_ER_BONQ] ,[B_ER_BONBACK] ,[B_ER_BONLM] ,[B_AC_BONS] ,[B_ER_OTFIX]
, [B_ER_BACKPAY] , [B_ER_OFFPRE1] ,[B_ER_OFFPRE2] ,[B_ER_OFFPRE3] ,[B_ER_CAR] ,[B_ER_TALENT] ,[B_AC_GROSS1] ,[B_DD_ABSENCE] , B_DD_ABS
,B_DD_SIC ,B_DD_AWAY ,B_DD_INJ ,B_DD_SICL ,B_DD_MAT ,B_ER_OTHPRE1 ,B_ER_OTHPRE2 ,B_AC_GROSS3 , B_DD_DONAT1 ,B_DD_DONAT2 ,B_VR_SIPHF_EE
,B_DD_UNIEE ,B_AC_TAXPRE ,B_BR_EXEMPTION ,B_AC_TAXINCOME ,B_DD_TAXNORM ,B_AC_TAXAFT ,B_ER_ALLBIRT ,B_ER_EXPMEDI ,B_ER_COMPDIS ,B_ER_INSPENS
,B_ER_INSMEDI ,B_ER_KAYAFT ,B_ER_RETAFT ,B_ER_OTHAFT1 ,B_ER_OTHAFT2 , B_DD_FEEFIRS ,B_DD_FEESECO ,B_DD_TAXREP ,B_ER_RETPRE ,B_ER_KAYPRE
,B_ER_SIGHON ,B_ER_SAL13 ,B_DD_TAXHOLD ,B_ER_COMPECO , B_FM_NET , B_FM_NETCNY ,B_VR_PRE_NET ,B_ER_BONY1
,B_ER_BONY2 ,B_ER_BONY3 ,B_DD_UNIER ,B_VR_SIAGENT ,B_PAYROLL_FREE1 ,B_PAYROLL_FREE2 ,B_PAYROLL_FREE3 )) AS PVT1
UNION ALL
SELECT EMPLID , CAL_RUN_ID , FIELDNAME , AMOUNT
FROM PS_CT_PAYROLL_WA UNPIVOT ( AMOUNT FOR FIELDNAME IN ( [B_BASEFO] , [B_BONYPC] , B_CAR ,B_NET ,B_NETCHN ,B_NETFOR ,B_OFFSI1 ,B_OFFSI2 ,B_OTFIX ,B_RATEF ,B_TAXREP )) AS PVT2
) A
INNER JOIN PS_CT_SCHECK_TBL B ON A.CAL_RUN_ID = B.CAL_RUN_ID
WHERE A.AMOUNT <> 0
UNION ALL
SELECT D.CT_MONTH , D.EMPLID , 'COMMENTS_250' AS FIELDNAME , D.COMMENTS_256
FROM PS_CT_PAYMAIN D
WHERE D.COMMENTS_256 IS NOT NULL
AND D.COMMENTS_256 <> ''
UNION ALL
SELECT A.CT_MONTH , A.EMPLID , B.CT_REFIELD , CONVERT(VARCHAR(254) , A.AMOUNT) AS COMMENT1 FROM PS_CT_MONTHADJ_TBL A
INNER JOIN PS_CT_PAYITEM_TBL B ON A.CT_SALARYTYPE = A.CT_SALARYTYPE AND A.CT_SALARYITEM = B.CT_SALARYITEM
WHERE A.AMOUNT <> 0
UNION ALL
SELECT F.CT_MONTH , F.EMPLID , 'COMMENT10' AS FIELDNAME , F.COMMENT1
FROM PS_CT_OTSALARY_TBL F
WHERE F.COMMENT1 IS NOT NULL AND F.COMMENT1 <> ''
UNION ALL
SELECT PP.CT_MONTH , PP.EMPLID , PP.FIELDNAME , CONVERT(NVARCHAR(254) , CONVERT(DECIMAL(18,2) ,PP.AMOUNT)) AS COMMENT1
FROM ( SELECT CT_MONTH , STATUS , CT_STATUS , EMPLID , FIELDNAME , AMOUNT
FROM PS_CT_PAYMAIN E
UNPIVOT ( AMOUNT FOR FIELDNAME IN ( B_PENS_EE , B_UNEMP_EE , B_MEDIC_EE , B_PHF_EE ,B_LARGE_EE ,B_OTHER_EE ,B_SIPHF_EE ,B_PENS_AMT_EE ,B_PENS_ADD_EE
,B_UNEMP_AMT_EE ,B_UNEMP_ADD_EE ,B_MEDIC_AMT_EE ,B_MEDIC_ADD_EE ,B_PHF_AMT_EE ,B_PHF_ADD_EE ,B_LARGE_AMT_EE ,B_LARGE_ADD_EE ,B_OTHER_AMT_EE ,B_OTHER_ADD_EE )) AS PVT5 )
AS PP
WHERE PP.AMOUNT <> 0
AND PP.STATUS = 'A'
AND PP.CT_STATUS = 5 )
AA INNER JOIN PSOPRDEFN BB ON AA.EMPLID = BB.EMPLID INNER JOIN PS_CT_SALFIELD_TBL CC ON AA.FIELDNAME = CC.FIELDNAME
ORDER BY CC.SORT_FIELD