写完后写在最前面的话:本来以为能够自己来原创一篇,但是再重新读了一下原作的博文,决定还是转载人家的吧。自己没有那个水平。。出处:原文链接
PIVOT是SQL Server2005新添加的一个表运算符,作用在于将行转为列。
首先上链接sql文档
然后摘出来pivot的语法:
SELECT <非透视的列>,
[第一个透视的列] AS <列名称>,
[第二个透视的列] AS <列名称>,
...
[最后一个透视的列] AS <列名称>,
FROM
(<生成数据的 SELECT 查询>)
AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ( [第一个透视的列], [第二个透视的列],
... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;
虽然并不能看出这个代码最后成为一篇博文是啥样,但是就当它排列整齐了吧。
举个栗子:
比如:有这么一张表:
下面这个查询将返回不同城市,地区的消费者的订单数:
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一样的透视结果。
而隐式分组的列的行值将作为透视行连同透视列一起组成透视表。