锋利的SQL-SQL Server的表旋转（行列转换）

所谓表旋转，就是将表的行转换为列，或是将表的列转换为行，这是从SQL Server 2005开始提供的新技术。因此，如果希望使用此功能，需要将数据库的兼容级别设置为90。表旋转在某些方面也是解决了表的数据存储和实际需要之间的矛盾。例如，图9-4所示的是一个典型的产品销售统计表，这种格式虽然便于阅读，但是在进行数据表存储的时候却并不容易管理，产品销售数据表通常需要设计成图9-5所示的结构。这样就带来一个问题，用户既希望数据容易管理，又希望能够生成一种能够容易阅读的表格数据，这时候就可以使用表旋转技术。

          

  图9-4 产品销售表     

  图9-5 数据表结构

9.4.1 PIVOT运算符

PIVOT运算符用于将表的行转换为列，并能同时对行执行聚合运算。其语法格式如下：

SELECT <非旋转列>,

    [第一个旋转列] AS <列名>,

    [第二个旋转列] AS <列名>,

    ...

    [最后的旋转列] AS <列名>

FROM

    (<SELECT 生成数据的查询>) AS <为源查询结果指定的别名>

 

PIVOT

(

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

FOR

[<包含将被转换为列标头的值的列>]

    IN ( [第一个旋转后的列], [第二个旋转后的列],

    ... [最后一个旋转后的列])

) AS <为旋转表指定的别名>

 

<可选的 ORDER BY 子句>;

为了实现行的旋转，源查询获得的结果应当具备三列，才能够实现旋转。第1列是不进行旋转的列，属于标志列；第2列是属性列，也称为透视列，其中的值会被旋转列名；第3列是属性值列，这些值将作为新列的值。使用下面的语句创建一个示例表Orders，内容如表9-7所示。

CREATE TABLE Orders

(

   ProductID int NOT NULL,

   OrderDate datetime NOT NULL,

   ShipTo char(20) NOT NULL,

   SubTotal money NOT NULL

);

INSERT INTO Orders

VALUES (1,CAST('20090102' AS datetime), 'Shanghai', 100.00),

       (1, CAST('20090105' AS datetime), 'Shanghai',100.00),

       (1, CAST('20090123' AS datetime),'Jinan',    100.00),

       (2, CAST('20090125' AS datetime),'Shanghai', 100.00),

       (1, CAST('20090205' AS datetime),'Jinan',    100.00),

       (3, CAST('20090213' AS datetime),'Shanghai', 100.00),

       (3, CAST('20090219' AS datetime),'Shanghai', 100.00),

       (4, CAST('20090309' AS datetime),'Beijing',  100.00),

       (1, CAST('20090311' AS datetime),'Dalian',   100.00),

       (2, CAST('20090324' AS datetime),'Shanghai', 100.00),

       (3, CAST('20090326' AS datetime),'Wuhan',    100.00);

表9-7                                                                     Orders表的内容

ProductID	OrderDate	ShipTo	SubTotal
1	2009-01-02 00:00:00.000	Shanghai	100.00
1	2009-01-05 00:00:00.000	Shanghai	100.00
1	2009-01-23 00:00:00.000	Jinan	100.00
2	2009-01-25 00:00:00.000	Shanghai	100.00
1	2009-02-05 00:00:00.000	Jinan	100.00
3	2009-02-13 00:00:00.000	Shanghai	100.00
3	2009-02-19 00:00:00.000	Shanghai	100.00
4	2009-03-09 00:00:00.000	Beijing	100.00
1	2009-03-11 00:00:00.000	Dalian