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

原创 2011年01月18日 16:03:00

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表中的内容
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表
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得到的结果集
ProductID
OrderMonth
SubTotal
1
五月
100
1
六月
100
1
七月
0
2
五月
200
2
六月
200
2
七月
200
3
五月
800
3
六月
0
3
七月
0

SQL Server 2005 中行列转换(Pivot 和 UNPivot的使用)

针对sql2005  系统提供两个新的关键字 PIVOT 和UNPIVOT可用来作此类操作. 语法规则 ::=         ( aggregate_function ( value_column...

SQL Server 2005之PIVOT/UNPIVOT行列转换(转)

SQL Server2005引入了很多迎合开发者口味的新特性,虽然改动不大,却大大了减少了开发者的工作量,这种替用户考虑的开发思路,值得称赞。在SQL Server2000中,要实现行列转换,需要...

SQL Server 2005之PIVOT/UNPIVOT行列转换

SQL Server2005引入了很多迎合开发者口味的新特性,虽然改动不大,却大大了减少了开发者的工作量,这种替用户考虑的开发思路,值得称赞。 在SQL Server2000中,要实现行列转换,需要...

Sql Server函数大全(二)---SQL Server 2005 中行列转换(Pivot 和 UNPivot的使用)

Sql Server函数大全(二)---SQL Server 2005 中行列转换(Pivot 和 UNPivot的使用)
  • cpcpc
  • cpcpc
  • 2011年02月28日 11:19
  • 1005

sql server 行列转换

  • 2011年12月30日 14:40
  • 375B
  • 下载

锋利的SQL-SQL Server的表旋转(行列转换)

所谓表旋转,就是将表的行转换为列,或是将表的列转换为行,这是从SQL Server 2005开始提供的新技术。因此,如果希望使用此功能,需要将数据库的兼容级别设置为90。表旋转在某些方面也是解决了表的...

Sql Server 2005 实现Oracle 10g 的hash表分区功能

最近学习Oracle中,在其有hash表分区功能,就在想sql server 有类似分区吗,查了一些资料,发现没有介绍,在2005中目前只支持列表和范围分区。        以前只知道Sql Se...

SQL Server 2005中的分区表(六):将已分区表转换成普通表

我的俄罗斯名叫作“不折腾不舒服斯基”,所以,不将分区表好好折腾一下,我就是不舒服。    在前面,我们介绍过怎么样直接创建一个分区表,也介绍过怎么将一个普通表转换成一个分区表。那么,这两种方式创建的表...
  • lary_li
  • lary_li
  • 2011年07月13日 14:44
  • 161

SQL Server 2005中的分区表(六):将已分区表转换成普通表

在前面,我们介绍过怎么样直接创建一个分区表,也介绍过怎么将一个普通表转换成一个分区表。那么,这两种方式创建的表有什么区别呢?现在,我又最新地创建了两个表:     第一个表名为Sale,这个...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:在SQL Server 2005中实现表的行列转换
举报原因:
原因补充:

(最多只允许输入30个字)