SQL 2005 PIVOT多欄彙總

微軟的SQL Server產品在SQL 2005開始就支援PIVOT，但從線上叢書通常看到PIVOT範例只有一個彙總欄位，以下的語法是利用SQL 2005的範例資料庫AdventureWorks，呈現2001-07-01到2001-07-07銷售明細產品每日的訂購數加總及產品每日的銷售加總

SELECT

ProductName,

SUM(ISNULL([1_Q], 0)) [1_Q], SUM(ISNULL([2_Q], 0)) [2_Q], SUM(ISNULL([3_Q], 0)) [3_Q], SUM(ISNULL([4_Q], 0)) [4_Q], SUM(ISNULL([5_Q], 0)) [5_Q], SUM(ISNULL([6_Q], 0)) [6_Q], SUM(ISNULL([7_Q], 0)) [7_Q],

SUM(ISNULL([1_A], 0)) [1_A], SUM(ISNULL([2_A], 0)) [2_A], SUM(ISNULL([3_A], 0)) [3_A], SUM(ISNULL([4_A], 0)) [4_A], SUM(ISNULL([5_A], 0)) [5_A], SUM(ISNULL([6_A], 0)) [6_A], SUM(ISNULL([7_A], 0)) [7_A]

FROM

(

SELECT

p.Name AS ProductName,

SUM(d.OrderQty) AS OrderQty,

SUM(d.LineTotal) AS LineTotal,

CAST(DAY(d.ModifiedDate) AS VARCHAR) + '_Q' AS D1,

CAST(DAY(d.ModifiedDate) AS VARCHAR) + '_A'AS D2

FROM Sales.SalesOrderDetail AS d

INNER JOIN Production.Product AS p ON d.ProductID = p.ProductID

WHERE d.ModifiedDate BETWEEN '2001-07-01' AND '2001-07-07'

GROUP BY p.Name, d.ModifiedDate

) p

PIVOT

(

SUM (OrderQty)

FOR D1 IN

( [1_Q], [2_Q], [3_Q], [4_Q], [5_Q], [6_Q], [7_Q])

) AS pvt1

PIVOT

(

SUM(LineTotal)

FOR D2 IN

( [1_A], [2_A], [3_A], [4_A], [5_A], [6_A], [7_A])

) AS pvt2

GROUP BY ProductName

上面的語法有用到一些小技巧，因為要求出每日的訂購數及銷售加總，所以日期欄位需要二個，一個給訂購數一個給銷售加總，這裡我們用D1代表訂購數的日期D2代表銷售加總的日期，另外SELECT清單需要14個欄位，其中7個欄位代表1~7日的訂購數，其餘7個欄位代表1~7日的銷售加總數，為了有所區別在訂購數的日期後加上_Q如1_Q，銷售加總日期後加上_A如1_A，最後注意在SUM函數裡要用ISNULL避免加總時因為有值為NULL造成結果為NULL。