三、 PIVOT Operator
PIVOT的中文意思是“在枢轴上转动”,比如对于一个2维坐标,将横坐标变成纵坐标,将纵坐标变成横坐标。反映在一个Relational Table上的意思就是:变成为列,变列为行。相信大家在进行报表设计的时候都遇到过类似于这样的需求:统计2002年内某个销售人员第一季度每个月处理的订单数。在AdventureWorks Sample Databse中,Sales Order存储于SaleOrderHeader这张表中,它的结果如下:
我们一般情况下通过下面的SQL实现我们提出的统计功能:
SUM ( CASE DATEPART (MM,OrderDate) WHEN 1 THEN 1 ELSE 0 END ) AS JAN,
SUM ( CASE DATEPART (MM,OrderDate) WHEN 2 THEN 1 ELSE 0 END ) AS FEB,
SUM ( CASE DATEPART (MM,OrderDate) WHEN 3 THEN 1 ELSE 0 END ) AS MAR,
SUM ( CASE DATEPART (MM,OrderDate) WHEN 4 THEN 1 ELSE 0 END ) AS APR
FROM Sales.SalesOrderHeader
WHERE DATEPART (yyyy,OrderDate) = 2002
GROUP BY SalesPersonID
于是我们得到了这样的统计数据:
通过数据在原表的结构和我们最终获得的结果进行比较,我们发现就像是“旋转”了90度,原来的OrderDate是存储在每行的基于Order的一个属性(行),现在我们要把Order Date按照不同月份统计,这样行变成了列。
像这样的需求,我们都可以可以通过PIVOT这个操作符来实现,下面就是基于PIVOT的SQL:SELECT语句筛选出来的是为经过PIVOT的数据。
PIVOT(COUNT(MON)是我们需要统计的数据,FOR MON IN ([1],[2],[3],[4]是统计的范围)就成了我们最终输出的结构了。
PIVOT,可以不能一下明白它的实现,但是只要你是使用了一两次,相信就会很容易地掌握它。与PIVOT对应的还以一个操作符UNPIVOT,它完成PIVOT的逆操作,在这里就不介绍了,如果有兴趣的话,可以参考SQL Server Books Online。
FROM
(
SELECT SalesPersonID, DATEPART (MM,OrderDate) AS MON
FROM Sales.SalesOrderHeader
WHERE DATEPART (yyyy,OrderDate) = 2002
) S
PIVOT
(
COUNT (MON) FOR MON IN ( [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )
)
AS P
在上面的例子中,同过下面的
FROM Sales.SalesOrderHeader
WHERE DATEPART (yyyy,OrderDate) = 2002
通过下面的
(
COUNT (MON) FOR MON IN ( [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )
)
如果你第一次见到