浙江工贸学院——SQL Server数据库应用技术

在 SQL Server 2005 中实现表的行列转换

　　PIVOT 和 UNPIVOT 关系运算符是 SQL Server 2005 提供的新增功能，因此，对升级到 SQL Server 2005 的数据库使用 PIVOT 和 UNPIVOT 时，数据库的兼容级别必须设置为 90 （可以使用 sp_dbcmptlevel 存储过程设置兼容级别）。

　　在查询的 FROM 子句中使用 PIVOT 和 UNPIVOT ，可以对一个输入表值表达式执行某种操作，以获得另一种形式的表。 PIVOT 运算符将输入表的行旋转为列，并能同时对行执行聚合运算。而 UNPIVOT 运算符则执行与 PIVOT 运算符相反的操作，它将输入表的列旋转为行。

在 FROM 子句中使用 PIVOT 和 UNPIVOT 关系运算符时的语法格式如下：

[ FROM { ＜ table_source ＞＝ [ ,...n ] ＝

＜ table_source ＞ ::= {

table_or_view_name [ [ AS ] table_alias ]

　＜ pivoted_table ＞ | ＜ unpivoted_table ＞

＝

＜ pivoted_table ＞ ::=table_source PIVOT ＜ pivot_clause ＞ table_alias

＜ pivot_clause ＞ ::=( aggregate_function ( value_column )

FOR pivot_column

IN ( ＜ column_list ＞＝

＝

＜ unpivoted_table ＞ ::=table_source UNPIVOT ＜ unpivot_clause ＞ table_alias

＜ unpivot_clause ＞ ::=( value_column FOR pivot_column IN ( ＜ column_list ＞＝＝

＜ column_list ＞ ::= column_name [ , ... ] table_source PIVOT ＜ pivot_clause ＞

　　指定对 table_source 表中的 pivot_column 列进行透视。 table_source 可以是一个表、表表达式或子查询。

aggregate_function

系统或用户定义的聚合函数。注意：不允许使用 COUNT(*) 系统聚合函数。

value_column

PIVOT 运算符用于进行计算的值列。与 UNPIVOT 一起使用时， value_column 不能是输入 table_source 中的现有列的名称。

FOR pivot_column

PIVOT 运算符的透视列。 pivot_column 必须是可隐式或显式转换为 nvarchar() 的类型。

使用 UNPIVOT 时， pivot_column 是从 table_source 中提取输出的列名称， table_source 中不能有该名称的现有列。

IN ( column_list )

在 PIVOT 子句中， column_list 列出 pivot_column 中将成为输出表的列名的值。

在 UNPIVOT 子句中， column_list 列出 table_source 中将被提取到单个 pivot_column 中的所有列名。

table_alias

输出表的别名。

UNPIVOT ＜ unpivot_clause ＞

　　指定将输入表中由 column_list 指定的多个列的值缩减为名为 pivot_column 的单个列。

　　常见的可能会用到 PIVOT 的情形是：需要生成交叉表格报表以汇总数据。交叉表是使用较为广泛的一种表格式，例如，图 5-4 所示的产品销售表就是一个典型的交叉表，其中的月份和产品种类都可以继续添加。但是，这种格式在进行数据表存储的时候却并不容易管理，要存储图 5-4 这样的表格数据，数据表通常需要设计为图 5-5 这样的结构。这样就带来一个问题，用户既希望数据容易管理，又希望能够生成一种能够容易阅读的表格数据。好在 PIVOT 为这种转换提供了便利。

　　假设 Sales.Orders 表中包含有 ProductID （产品 ID ）、 OrderMonth （销售月份）和 SubTotal （销售额）列，并存储有如表 5-2 所示的内容。

　　表 5-2 Sales.Orders 表中的内容

ProductID	OrderMonth	SubTotal
1	5	100.00
1	6	100.00
2	5	200.00
2	6	200.00
2	7	300.00
3	5	400.00
3	5	400.00

　　执行下面的语句：

SELECT ProductID, [5] AS 五月 , [6] AS 六月 , [7] AS 七月

FROM

Sales.Orders PIVOT

(

SUM (Orders.SubTotal)

FOR Orders.OrderMonth IN

( [5], [6], [7] )

) AS pvt

ORDER BY ProductID;

　　在上面的语句中， Sales.Orders 是输入表， Orders.OrderMonth 是透视列（ pivot_column ）， Orders.SubTotal 是值列（ value_column ）。上面的语句将按下面的步骤获得输出结果集：

a ． PIVOT 首先按值列之外的列（ ProductID 和 OrderMonth ）对输入表 Sales.Orders 进行分组汇总，类似执行下面的语句：

SELECT ProductID,

OrderMonth,

SUM (Orders.SubTotal) AS SumSubTotal

FROM Sales.Orders

GROUP BY ProductID,OrderMonth;

　　这时候将得到一个如表 5-3 所示的中间结果集。其中只有 ProductID 为 3 的产品由于在 5 月有 2 笔销售记录，被累加到了一起（值为 800 ）。

表 5-3 Sales.Orders 表经分组汇总后的结果

ProductID	OrderMonth	SumSubTotal
1	5	100.00
1	6	100.00
2	5	200.00
2	6	200.00
2	7	300.00
3	5	800.00

b ． PIVOT 根据 FOR Orders.OrderMonth IN 指定的值 5 、 6 、 7 ，首先在结果集中建立名为 5 、 6 、 7 的列，然后从图 5-3 所示的中间结果中取出 OrderMonth 列中取出相符合的值，分别放置到 5 、 6 、 7 的列中。此时得到的结果集的别名为 pvt （见语句中 AS pvt 的指定）。结果集的内容如表 5-4 所示。

表 5-4 使用 FOR Orders.OrderMonth IN( [5], [6], [7] ) 后得到的结果集

ProductID	5	6	7
1	100.00	100.00	NULL
2	200.00	200.00	200.00
3	800.00	NULL	NULL

c ．最后根据 SELECT ProductID, [5] AS 五月 , [6] AS 六月 , [7] AS 七月 FROM 的指定，从别名 pvt 结果集中检索数据，并分别将名为 5 、 6 、 7 的列在最终结果集中重新命名为五月、六月、七月。这里需要注意的是 FROM 的含义，其表示从经 PIVOT 关系运算符得到的 pvt 结果集中检索数据，而不是从 Sales.Orders 中检索数据。最终得到的结果集如表 5-5 所示。

表 5-5 由表 5-2 所示的 Sales.Orders 表将行转换为列得到的最终结果集

ProductID	五月	六月	七月
1	100.00	100.00	NULL
2	200.00	200.00	200.00
3	800.00	NULL	NULL

　　UNPIVOT 与 PIVOT 执行几乎完全相反的操作，将列转换为行。但是， UNPIVOT 并不完全是 PIVOT 的逆操作，由于在执行 PIVOT 过程中，数据已经被进行了分组汇总，所以使用 UNPIVOT 并不会重现原始表值表达式的结果。假设表 5-5 所示的结果集存储在一个名为 MyPvt 的表中，现在需要将列标识符“五月”、“六月”和“七月”转换到对应于相应产品 ID 的行值（即返回到表 5-3 所示的格式）。这意味着必须另外标识两个列，一个用于存储月份，一个用于存储销售额。为了便于理解，仍旧分别将这两个列命名为 OrderMonth 和 SumSubTotal 。参考下面的语句：

CREATE TABLE MyPvt (ProductID int, 五月 int, 六月 int, 七月 int); -- 建立 MyPvt 表

-- 将表 5-5 中所示的值插入到 MyPvt 表中

INSERT INTO MyPvt VALUES (1,100,100,0);

INSERT INTO MyPvt VALUES (2,200,200,200);

INSERT INTO MyPvt VALUES (3,800,0,0);

-- 执行 UNPIVOT

SELECT ProductID, OrderMonth, SubTotal

FROM

MyPvt UNPIVOT

(SubTotal FOR OrderMonth IN

( 五月 , 六月 , 七月 )

)AS unpvt;

　　上面的语句将按下面的步骤获得输出结果集：

a ．首先建立一个临时结果集的结构，该结构中包含 MyPvt 表中除 IN ( 五月 , 六月 , 七月 ) 之外的列，以及 SubTotal FOR OrderMonth 中指定的值列（ SubTotal ）和透视列（ OrderMonth ）。

b ．将在 MyPvt 中逐行检索数据，将表的列名称（在 IN ( 五月 , 六月 , 七月 ) 中指定）放入 OrderMonth 列中，将相应的值放入到 SubTotal 列中。最后得到的结果集如表 5-6 所示。

表 5-6 使用 UNPIVOT 得到的结果集

ProductID	OrderMonth	SubTotal
1	五月	100
1	六月	100
1	七月	0
2	五月	200
2	六月	200
2	七月	200
3	五月	800
3	六月	0
3	七月	0