T-SQL Part IX, PIVOT and UNPIVOT
2024-09-01 17:27:34
不同于CROSS JOIN, CROSS APPLY, OUTER APPLY,MSDN文档对PIVOT和UNPIVOT 想得重视了一点,单独做了一个页面来介绍。
简单来说,PIVOT用来把行转成列,而UNPIVOT可以把列转成行。
用MSDN文档给出的两个例子来做说明。
例一,基础示例。
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[], [], [], [], []
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([], [], [], [], [])
) AS PivotTable;
例二,复杂示例。
USE AdventureWorks2014;
GO
SELECT VendorID, [] AS Emp1, [] AS Emp2, [] AS Emp3, [] AS Emp4, [] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [], [], [], [], [] )
) AS pvt
ORDER BY pvt.VendorID;
第一步,也是所有SELECT语句的第一步,弄清楚Source Table,即FROM后面的源table。
对于例一:
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
对于例二:
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
第二步,理解PIVOT的作用域。
对于例一,即针对DaysToManufacture在IN范畴中的每个值计算AVG(StandardCost)
(
AVG(StandardCost)
FOR DaysToManufacture IN ([], [], [], [], [])
)
对于例二,即针对EmployID在IN范畴中的每一个值计算COUNT (PurchaseOrderID):
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [], [], [], [], [] )
)
事实上,可以用等价的SQL来实现PIVOT。以下两段SQL是等价的:
SELECT empid, [], [], []
FROM ( SELECT empid, YEAR(orderdate) AS orderyear, val
FROM Sales.OrderValues ) AS D
PIVOT( SUM(val) FOR orderyear IN([],[],[]) ) AS P;
SELECT empid,
SUM(CASE WHEN orderyear = 2013 THEN val END) AS [],
SUM(CASE WHEN orderyear = 2014 THEN val END) AS [],
SUM(CASE WHEN orderyear = 2015 THEN val END) AS []
FROM ( SELECT empid, YEAR(orderdate) AS orderyear, val
FROM Sales.OrderValues ) AS D
GROUP BY empid;
UNPIVOT执行的是PIVOT相反但原理完全一致的操作。
然而,有两个问题比较困扰我:
- 如何执行动态的PIVOT?比如,示例中的IN()部分都是写死的,现实中显然这种固定值的情况不多。搜索了一圈下来,答案几乎全都是手动拼接IN后面的字符串。有一篇Blog值得一看。
- 这个功能在现实中的意义?貌似这个没有标准答案。无论如何,行列互转总是个噱头。
是为之记。
Alva Chien
2016.6.14
最新文章
- CentOS 7.0系统安装配置步骤详解
- Sublime Text 2 windows8安装插件失败解决
- HTML5学习笔记二 HTML基础
- 利用OData轻易实现串流数据的可视化
- 看懂UML图
- 《Code Complete》ch.25 代码调整策略
- 简短总结一下C#里跨线程更新UI
- Android开发手记(31) 使用MediaRecorder录音
- CentOS6.3 下启动Oracle service和listener
- Effective C++ 第二版 1)const和inline 2)iostream
- Effective Modern C++ Item 27:重载universal references
- ST-3- Installing and Testing IUnit, Hamcrest and Eclemma
- JMeter——简单的接口测试实例(一)
- matlab中的实时音频
- STS(Spring Tool Suite)下SSM(Spring+SpringMVC+Mybatis)框架搭建(一)
- SQLMAP注入教程-11种常见SQLMAP使用方法详解
- (PMP)第5章-----项目范围管理
- CSS 媒体查询@media
- poj 3013 最短路变形
- 基于Spring-Boot框架的Elasticsearch搜索服务器配置