|
Execute the following
Microsoft SQL Server T-SQL scripts in SSMS Query Editor to create a crosstab report on Purchase Orders by the application of the PIVOT operator and Total Sales crosstab by using the CASE function.
-- SQL pivot rows into column - sql pivot crosstab – crosstab query sql server
-- MSSQL create stored procedure - create stored procedure microsoft sql server
USE AdventureWorks2008;
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.BusinessEntityID = poh.VendorID
-- SQL wildcard selection
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 -- SQL Server pivot operator - pivot command sql server
(SUM(TotalDue)
FOR OrderMonth IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] ) )
AS CrossTab
END
GO
-- T-SQL execute stored procedure - execute stored procedure sql server 2008
EXEC MonthlyPurchaseOrder 2004
GO
/* Partial results
VendorName Jan Feb Mar
Capital Road Cycles $45,314.14 $60,418.86 $60,418.86
Carlson Specialties $29,107.42 $54,343.56 $36,229.04
Chicago City Saddles $137,282.52 $281,003.85 $293,824.03
Chicago Rent-All $1,829.48 $2,439.31 $2,439.31
*/
------------
-- SQL Crosstab Query - CASE Function - sql server crosstab query
------------
-- Sales total crosstab by year by country - SUM aggregate function
USE AdventureWorks2008;
SELECT Territory = LEFT(t.Name,20),
'$' + convert(VARCHAR,SUM(CASE
WHEN YEAR(OrderDate) = 2001 THEN TotalDue
ELSE 0
END),1) AS YY2001,
'$' + convert(VARCHAR,SUM(CASE
WHEN YEAR(OrderDate) = 2002 THEN TotalDue
ELSE 0
END),1) AS YY2002,
'$' + convert(VARCHAR,SUM(CASE
WHEN YEAR(OrderDate) = 2003 THEN TotalDue
ELSE 0
END),1) AS YY2003,
'$' + convert(VARCHAR,SUM(CASE
WHEN YEAR(OrderDate) = 2004 THEN TotalDue
ELSE 0
END),1) AS YY2004
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesTerritory t
ON soh.TerritoryID = t.TerritoryID
GROUP BY LEFT(t.Name,20)
ORDER BY Territory
/*
Territory YY2001 YY2002 YY2003 YY2004
Australia $1,446,497.17 $2,380,484.84 $4,547,123.28 $3,823,410.24
Canada $2,173,647.15 $7,215,430.50 $8,186,021.92 $3,926,712.89
Central $1,263,884.10 $3,518,185.48 $4,015,356.87 $1,771,532.74
France $199,531.72 $1,717,145.74 $4,366,078.35 $2,853,948.66
Germany $262,752.42 $575,960.10 $2,714,826.43 $2,386,224.55
Northeast $754,833.20 $3,275,322.17 $3,833,030.25 $1,406,555.69
Northwest $2,703,481.79 $5,651,688.67 $7,494,658.04 $4,952,772.28
Southeast $1,928,148.61 $3,814,944.14 $3,261,403.00 $1,566,084.67
Southwest $3,272,568.52 $9,947,538.71 $11,523,237.52 $6,470,114.83
United Kingdom $322,207.53 $1,778,804.75 $4,365,879.44 $3,039,555.88
*/
------------
|
