PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单、更具可读性。
常见的可能会用到 PIVOT 的情形是,需要生成交叉表格报表以汇总数据时。例如,假设需要在 AdventureWorks
示例数据库中查询 PurchaseOrderHeader
表以确定由某些特定雇员所下的采购订单数。以下查询提供了此报表(按供应商划分):
USE AdventureWorks; |
以下为部分结果集:
VendorID Emp1 Emp2 Emp3 Emp4 Emp5 |
将在 EmployeeID
列上透视此嵌套 select 语句返回的结果。
SELECT PurchaseOrderID, EmployeeID, VendorID |
这意味着 EmployeeID
列返回的唯一值自行变成了最终结果集中的字段。结果,在透视子句中指定的每个 EmployeeID
号(在本例中为雇员 164、198、223、231 和 233)都有相应的一列。PurchaseOrderID
列用作值列,将根据此列对最终输出中返回的列(称为分组列)进行分组。在本例中,通过 COUNT 函数聚合了分组列。请注意,将显示出一条警告消息,指明针对每个雇员计算 COUNT 时不考虑显示在 PurchaseOrderID
列中的任何 NULL 值。
重要事项: |
---|
如果 PIVOT 中使用聚合函数,则计算聚合时将不考虑出现在值列中的任何 NULL 值。
|
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 above example. CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, |
以下为部分结果集:
VendorID Employee Orders |
请注意,UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT 的输入中的 NULL 不会显示在输出中,然而在执行 PIVOT 操作之前输入中可能会含有原始的 NULL 值。
AdventureWorks 示例数据库中的 Sales.vSalesPersonSalesByFiscalYears 视图将使用 PIVOT 返回每个销售人员在每个会计年度的总销售额。若要对该视图编写脚本,请在 SQL Server Management Studio 对象资源管理器中 AdventureWorks 数据库对应的 Views 文件夹下找到它。然后右键单击该视图名称并选择“编写视图脚本为”。