SQL Server 2005中PIVOT與UNPIVOT

最新推荐文章于 2024-06-14 14:50:17 发布

翻译最新推荐文章于 2024-06-14 14:50:17 发布 · 2k 阅读

文章标签：

#pivot #sql server #table #insert #存储 #object

本文详细介绍了SQL Server 2005中新增的PIVOT和UNPIVOT功能，这两种关系运算符能够实现数据透视与反转操作。PIVOT能够将数据集中的行转换为列，而UNPIVOT则执行相反的操作。

PIVOT

PIVOT和UNPIVOT关系运算符是SQL Server 2005提供的新增功能，因此，对升级到SQL Server 2005的数据库使用PIVOT和UNPIVOT时，数据库的兼容级别必须设置为90（可以使用sp_dbcmptlevel存储过程设置兼容级别）。
在查询的FROM子句中使用PIVOT和UNPIVOT，可以对一个输入表值表达式执行某种操作，以获得另一种形式的表。PIVOT运算符将输入表的行旋转为列，并能同时对行执行聚合运算。而UNPIVOT运算符则执行与PIVOT运算符相反的操作，它将输入表的列旋转为行。
*/

-->Title:生成測試數據
-->Author:wufeng4552
-->Date :2009-09-14 09:37:37
if not object_id('Orders') is null
drop table Orders
Go
Create table Orders([ProductID] int,[OrderMonth] int,[SubTotal] decimal(18,2))
Insert Orders
select 1,5,100.00 union all
select 1,6,100.00 union all
select 2,5,200.00 union all
select 2,6,200.00 union all
select 2,7,300.00 union all
select 3,5,400.00 union all
select 3,5,400.00
Go
select [ProductID],
       isnull([5],0)[5月],
       isnull([6],0)[6月],
       isnull([7],0)[7月]
from
Orders pivot
(sum([SubTotal])
for [OrderMonth]
in([5],[6],[7]))as pvt
/*
ProductID   5月                                      6月                                      7月
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1           100.00                                  100.00                                  0.00
2           200.00                                  200.00                                  300.00
3           800.00                                  0.00                                    0.00

(3 個資料列受到影響)
*/
/*在上面的语句中，Orders是输入表，OrderMonth是透视列（pivot_column）

SubTotal是值列（value_column）。上面的语句将按下面的步骤获得输出结果集：

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

SELECT ProductID,
   OrderMonth,
   SUM (Orders.SubTotal) AS SumSubTotal
FROM Orders
GROUP BY ProductID,OrderMonth
/*
ProductID   OrderMonth SumSubTotal
----------- ----------- ---------------------------------------
1           5           100.00
2           5           200.00
3           5           800.00
1           6           100.00
2           6           200.00
2           7           300.00

(6 個資料列受到影響)
*/

b．PIVOT根据FOR Orders.OrderMonth IN指定的值5、6、7，首先在结果集中建立名为5、6、7的列，

上述所示的中间结果中取出OrderMonth列中取出相符合的值，分别放置到5、6、7的列中。

此时得到的结果集的别名为pvt（见语句中AS pvt的指定）

c．最后根据SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月FROM的指定，

从别名pvt结果集中检索数据，并分别将名为5、6、7的列在最终结果集中重新命名为五月、六月、七月。

这里需要注意的是FROM的含义，其表示从经PIVOT关系运算符得到的pvt结果集中检索数据，而不是从Orders中检索数据。

PIVOT 注意點

table_source PIVOT <pivot_clause>

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

不允许使用COUNT(*)系统聚合函数,可以用戶自定義

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

IN ( column_list )

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

UNPIVOT

UNPIVOT与PIVOT执行几乎完全相反的操作，将列转换为行。但是，UNPIVOT并不完全是PIVOT的逆操作，由于在执行PIVOT过程中，数据已经被进行了分组汇总，所以使用UNPIVOT并不会重现原始表值表达式的结果。

上述结果集存储在一个名为MyPvt的表中，现在需要将列标识符“五月”、“六月”和“七月”转换到对应于相应产品ID的行值（即返回到最初所示的格式）。这意味着必须另外标识两个列，一个用于存储月份，一个用于存储销售额。为了便于理解，仍旧分别将这两个列命名为OrderMonth和SumSubTotal。

IF OBJECT_ID('MYPVT')IS NOT NULL DROP TABLE MYPVT
GO
CREATE TABLE MyPvt (ProductID int, [5]int, [6] int,[7]int); --建立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([5],[6],[7]))
AS UNPVT
/*
ProductID   OrderMonth                                                                                                                       SubTotal
----------- -------------------------------------------------------------------------------------------------------------------------------- -----------
1           5                                                                                                                                100
1           6                                                                                                                                100
1           7                                                                                                                                0
2           5                                                                                                                                200
2           6                                                                                                                                200
2           7                                                                                                                                200
3           5                                                                                                                                800
3           6                                                                                                                                0
3           7                                                                                                                                0