|
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software
to demonstrate the applicatino of CTE with PIVOT.
-- SQL Common Table Expression - CTE - SQL PIVOT
-- SQL pivoting rows into columns - SQL pivot crosstab query
USE AdventureWorks;
WITH cteMonthlyPurchaseOrders
AS (SELECT VendorID,
OrderMonth = DATEPART(m,OrderDate),
SubTotal
FROM Purchasing.PurchaseOrderHeader
WHERE VendorID > 50
AND Year(OrderDate) = 2003)
SELECT Vendor = [Name],
-- SQL format money - currency conversion - USD
COALESCE('$'+convert(varchar,[1],1),'') AS [1]
,COALESCE('$'+convert(varchar,[2],1),'') AS [2]
,COALESCE('$'+convert(varchar,[3],1),'') AS [3]
,COALESCE('$'+convert(varchar,[4],1),'') AS [4]
,COALESCE('$'+convert(varchar,[5],1),'') AS [5]
,COALESCE('$'+convert(varchar,[6],1),'') AS [6]
,COALESCE('$'+convert(varchar,[7],1),'') AS [7]
,COALESCE('$'+convert(varchar,[8],1),'') AS [8]
,COALESCE('$'+convert(varchar,[9],1),'') AS [9]
,COALESCE('$'+convert(varchar,[10],1),'') AS [10]
,COALESCE('$'+convert(varchar,[11],1),'') AS [11]
,COALESCE('$'+convert(varchar,[12],1),'') AS [12]
FROM cteMonthlyPurchaseOrders po
PIVOT
(SUM(SubTotal)
FOR OrderMonth IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
AS X
JOIN Purchasing.Vendor v
ON X.VendorID = v.VendorID
ORDER BY Vendor
GO
/* Partial results
Vendor 1 2 3 4 5 6
American Bicycles and Wheels $171.08
American Bikes $20,397.30
Aurora Bike Center $278.52
Australia Bike Retailer $1,497.51
Bergeron Off-Roads $738.99
Bicycle Specialists $34,644.23
Bloomington Multisport $146.29
Carlson Specialties $6,444.90
Chicago Rent-All $551.88
Circuit Cycles $6,987.75
Crowley Sport $43,878.45
Cruger Bike Company $414.92
Custom Frames, Inc. $29,989.58
*/ |
