SQL Server 2005中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( int, int, 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 ,
isnull(,0),
isnull(,0),
isnull(,0)
from
Orders pivot
(sum()
for
in(,,))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, AS 五月, AS 六月, 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, int, int,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(,,))
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
(9 個資料列受到影響)
*/
/*
上面的语句将按下面的步骤获得输出结果集：
a．首先建立一个临时结果集的结构，该结构中包含MyPvt表中除IN (五月, 六月, 七月)之外的列，以及SubTotal FOR OrderMonth中指定的值列（SubTotal）和透视列（OrderMonth）。
b．将在MyPvt中逐行检索数据，将表的列名称（在IN (五月, 六月, 七月)中指定）放入OrderMonth列中，将相应的值放入到SubTotal列中。最后得到的结果集如表5-6所示。
*/
页:
[1]