SQL Server 学习之路(七)

317 阅读3分钟

最近重新看了一遍《MICROSOFT SQL SERVER 2008技术内幕:T-SQL语言基础》这本书,里面的有些知识,比如透视转换(PIVOT),逆透视转换(UNPIVOT)觉得比较经典,拿来给大家分享一下。

透视转换(PIVOT)

这个我在前面讲CASE的时候有介绍一个例子,就是行列转换。这里主要介绍PIVOT的具体使用方法及其里面的相关参数等。

PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。

以下是带批注的 PIVOT 语法。

SELECT <非透视的列>,

    [第一个透视的列] AS <列名称>,

    [第二个透视的列] AS <列名称>,

    ...

    [最后一个透视的列] AS <列名称>,

FROM

    (<生成数据的 SELECT 查询>)

    AS <源查询的别名>

PIVOT

(

    <聚合函数>(<要聚合的列>)

FOR

[<包含要成为列标题的值的列>]

    IN ( [第一个透视的列], [第二个透视的列],

    ... [最后一个透视的列])

) AS <透视表的别名>

<可选的 ORDER BY 子句>;

上面的语法看着有点别扭,不好理解,我们用一个例子来解释它的用法:\

简单 PIVOT 示例


下面的代码示例生成一个两列四行的表。

复制

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;\

下面是结果集:

DaysToManufacture          AverageCost

0                                       5.0885

1                                       223.88

2                                       359.1082

4                                       949.4105

没有定义 DaysToManufacture 为 3 的产品。

以下代码显示相同的结果,该结果经过透视以使 DaysToManufacture 值成为列标题。提供一个列表示三 [3] 天,即使结果为 NULL。

SELECT 'AverageCost' AS Cost,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;\

下面是结果集:

Cost                              0            1              2               3              4       

AverageCost        5.0885    223.88    359.1082    NULL    949.4105

\

对照上面的语法,我们就可以明确每个参数的具体意思,主要是FOR前面的第一个参数是需要聚合的列,这里取的是价格的评价值。FOR后面的参数是要成为列标题的值的列名称,然后IN里面就是各个需要透视的列。我是这么理解的,虽然开始不容易懂,但是用久了就知道是怎么一回事了。

逆透视转换(UNPIVOT)\

UNPIVOT 将与 PIVOT 执行几乎完全相反的操作,将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt,并且您需要将列标识符 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋转为对应于特定供应商的行值。这意味着必须标识另外两个列。包含要旋转的列值(Emp1、Emp2...)的列将被称为 Employee,将保存当前位于待旋转列下的值的列被称为 Orders。

其语法结构和PIVOT一样,只是功能不一样,我们也做个简单是示例给大家:\

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO\

以下为部分结果集。

VendorID Employee Orders

---------- ---------- ------

1          Emp1       4

1          Emp2       3

1          Emp3       5

1          Emp4       4

1          Emp5       4

2          Emp1       4

2          Emp2       1

2          Emp3       5

2          Emp4       5

2          Emp5       5

...

请注意,UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT 的输入中的空值不会显示在输出中,而在执行 PIVOT 操作之前,输入中可能有原始的空值。

这两个关键词的用法在平时处理数据时用的比较多,特别是一些数据的分类汇总,经常需要使用到透视和逆透视。等大家遇到的时候可以尝试着使用一下,可以减少很多代码。

\