在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为这种转换提供了便利。

　　图5-4 产品销售表 图5-5 数据表结构

　　假设sales.orders表中包含有productid（产品id）、ordermonth（销售月份）和subtotal（销售额）列，并存储有如表5-2所示的内容。

　　表5-2 sales.orders表中的内容

　　执行下面的语句：

　　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表经分组汇总后的结果

　　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] )后得到的结果集

　　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表将行转换为列得到的最终结果集

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

　　create table mypvt (productid int, 五月int, 六月 int, 七月int); --建立mypvt表
go
　　--将表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得到的结果集