Sql Server中不常用的表运算符之PIVOT
PIVOT是SQL Server2005新添加的一个表运算符,作用在于将行转为列。
先来看看他的基本语法:
来自http://technet.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx
SELECT <非透视的列>, [第一个透视的列] AS <列名称>, [第二个透视的列] AS <列名称>, ... [最后一个透视的列] AS <列名称>, FROM (<生成数据的 SELECT 查询>) AS <源查询的别名> PIVOT ( <聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列], [第二个透视的列], ... [最后一个透视的列]) ) AS <透视表的别名> <可选的 ORDER BY 子句>;
看到这一坨,我已经晕了,所以还是用一个实际的列子来理解。
假设有如下order表
下面这个查询将返回不同城市,地区的消费者的订单数
SELECT City,--可选非透视列 Region,--可选非透视列 C001,--透视列 C002,--透视列 C003--透视列 FROM ( SELECT City, Region, CustomId, OrderId FROM dbo.[Order] )AS OrderTable--源表 PIVOT ( COUNT(--聚合函数 OrderID--要聚合的列 ) FOR CustomId--要成为标题值的列 IN( [C001],--透视列 [C002],--透视列 [C003]--透视列 ) ) AS PivoTTable ORDER BY City DESC--可选Order by
结果如下
PIVOT将按一下三个步骤处理源表
1.隐式分组
源表中选出了四列,其中的两列作为PIVOT的输入参数:OrderID--要聚合的列,CustomId--要成为标题值的列
剩下的两列将作为源表的隐式分组,源表查询将会转换为如下查询
SELECT City, Region, OrderID, CustomID FROM dbo.[Order] GROUP BY City,Region
此时这个查询无法通过编译,因为我们还没有对OrderID和CustomID进行聚合操作。
2.隔离值
这个阶段将对透视列进行隔离,类似于下面的操作
SELECT City, Region, CASE WHEN CustomId ='C001' THEN OrderId END AS C001, CASE WHEN CustomId ='C002' THEN OrderId END AS C002, CASE WHEN CustomId ='C003' THEN OrderId END AS C003 FROM dbo.[Order] GROUP BY City,Region
3.对每个CASE表达式进行聚合操作,得到最终查询
SELECT City, Region, COUNT(CASE WHEN CustomId ='C001' THEN OrderId END) AS C001, COUNT(CASE WHEN CustomId ='C002' THEN OrderId END) AS C002, COUNT(CASE WHEN CustomId ='C003' THEN OrderId END) AS C003 FROM dbo.[Order] GROUP BY City,Region ORDER BY City DESC--可选的排序操作
最后执行上面这个查询,将得到与使用PIVOT一样的透视结果。
而隐式分组的列的行值将作为透视行连同透视列一起组成透视表。