Wednesday, September 3, 2008

PIVOT Query

USE AdventureWorks;
GO
CREATE PROC MonthlyPurchaseOrder @Year int
AS
BEGIN
DECLARE @MonthlyPurchaseOrders TABLE
(VendorName nvarchar(50),
OrderMonth int,
TotalDue Money)
INSERT @MonthlyPurchaseOrders
SELECT v.Name, DATEPART(Month, OrderDate), TotalDue
FROM Purchasing.Vendor v
JOIN Purchasing.PurchaseOrderHeader poh
ON v.VendorID = poh.VendorID
WHERE v.Name like ('[c-p]%')
AND YEAR(OrderDate)=@Year
SELECT VendorName, Jan='$'+convert(varchar,[1],1),
Feb='$'+convert(varchar,[2],1), Mar='$'+convert(varchar,[3],1), Apr='$'+convert(varchar,[4],1), May ='$'+convert(varchar,[5],1),
Jun='$'+convert(varchar,[6],1), Jul='$'+convert(varchar,[7],1), Aug='$'+convert(varchar,[8],1), Sep ='$'+convert(varchar,[9],1),
Oct='$'+convert(varchar,[10],1), Nov='$'+convert(varchar,[11],1), Dec='$'+convert(varchar,[12],1)
FROM @MonthlyPurchaseOrders
PIVOT (SUM(TotalDue) FOR OrderMonth In (
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS CrossTab
END
GO
EXEC MonthlyPurchaseOrder 2004
GO

No comments:

Follow by Email