在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中实现表的行列转换

在SQL Server 2005中实现表的行列转换  PIVOT和UNPIVOT关系运算符是SQL Server 2005提供的新增功能,因此,对升级到SQL Server 2005的数据库使用PIV...

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

PIVOT和UNPIVOT关系运算符是SQL Server 2005提供的新增功能,因此,对升级到SQL Server 2005的数据库使用PIVOT和UNPIVOT时,数据库的兼容级别必须设置为90...

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

在SQL Server 2005中实现表的行列转换   PIVOT和UNPIVOT关系运算符是SQL Server 2005提供的新增功能,因此,对升级到SQL Server 2...

SQL Server 2005之PIVOT/UNPIVOT行列转换

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

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

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

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
  • 970

sql server 行列转换

  • 2011-12-30 14:40
  • 375B
  • 下载

SQL Server中行列转换 Pivot UnPivot

(转自cnblog张志涛)    PIVOT用于将列值旋转为列名(即行转列),在SQL Server2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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