SQL Server 2005中PIVOT與UNPIVOT

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

SELECT ProductID,

   OrderMonth,

   SUM (Orders.SubTotal) AS SumSubTotal

FROM Orders

GROUP BY ProductID,OrderMonth

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