|
Execute the following Microsoft SQL Server 2005 T-SQL script in Management Studio Query Editor to generate a BI report with 2-level vertical dimension(category and product) and 1-level horizontal dimension(year). The tabulated measure is orders received in dollars (convert money to currency):
USE AdventureWorks;
-- SQL Common Table Expression - CTE - SQL pivot – crosstab / matrix report
-- SQL inner join - T-SQL currency formatting
WITH cteProductSales(Category,ProductName,ProductID,OrderYear,OrderTotal)
AS (SELECT c.Name,
p.Name,
sod.ProductID,
YEAR(soh.OrderDate),
convert(MONEY,sod.LineTotal)
FROM Production.ProductSubCategory c
INNER JOIN Production.Product p
ON c.ProductSubCategoryID = p.ProductSubCategoryID
INNER JOIN Sales.SalesOrderDetail sod
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesorderID = soh.SalesorderID)
SELECT psPIVOT.Category,
psPIVOT.ProductName,
Y2001 = '$' + convert(VARCHAR,isnull([2001],0),1),
Y2002 = '$' + convert(VARCHAR,isnull([2002],0),1),
Y2003 = '$' + convert(VARCHAR,isnull([2003],0),1),
Y2004 = '$' + convert(VARCHAR,isnull([2004],0),1)
FROM cteProductSales
PIVOT
(SUM(OrderTotal)
FOR OrderYear IN ( [2001],[2002],[2003],[2004] ) ) AS psPIVOT
ORDER BY psPIVOT.Category,
psPIVOT.ProductName;
GO
/* Partial results
Category ProductName Y2001 Y2002 Y2003 Y2004
Bib-Shorts Men's Bib-Shorts, L $0.00 $22,839.46 $11,338.74 $0.00
Bib-Shorts Men's Bib-Shorts, M $0.00 $52,236.61 $33,578.47 $350.96
Bib-Shorts Men's Bib-Shorts, S $0.00 $26,786.64 $20,310.74 $116.99
Bike Racks Hitch Rack - 4-Bike $0.00 $0.00 $134,868.47 $102,227.69
Bike Stands All-Purpose Bike Stand $0.00 $0.00 $18,921.00 $20,670.00
Bottles and Cages Mountain Bottle Cage $0.00 $0.00 $8,231.76 $11,997.99
*/
------------
|
