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。
