|
Execute the following
T-SQL script in Microsoft SQL Server Management Studio Query Editor to demonstrate the usage of the CTE-s and PIVOT for complex crosstab reporting:
-- SQL common table expression - CTE
-- T-SQL pivot operator - crosstab query - pivot rows into columns
-- MSSQL - pivot PO count and PO sum of TotalDue - double pivot - multiple pivots
USE AdventureWorks;
GO
WITH cteVendorPO
AS (SELECT PurchaseOrderID,
City = City,
State = StateProvinceCode,
Employee = FirstName + ' ' + LastName,
Vendor = v.Name
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
INNER JOIN Purchasing.Vendor v
ON v.VendorID = poh.VendorID
INNER JOIN Purchasing.VendorAddress va
ON v.VendorID = va.VendorID
INNER JOIN Person.Address a
ON a.AddressID = va.AddressID
INNER JOIN Person.StateProvince sp
ON sp.StateProvinceID = a.StateProvinceID),
cteVendorTotalDue
AS (SELECT Employee = FirstName + ' ' + LastName,
City = City,
State = StateProvinceCode,
Vendor = v.Name,
TotalDue
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
INNER JOIN Purchasing.Vendor v
ON v.VendorID = poh.VendorID
INNER JOIN Purchasing.VendorAddress va
ON v.VendorID = va.VendorID
INNER JOIN Person.Address a
ON a.AddressID = va.AddressID
INNER JOIN Person.StateProvince sp
ON sp.StateProvinceID = a.StateProvinceID)
SELECT pvt1.State,
pvt1.City,
pvt1.Vendor,
[Erin Hagens POs] = pvt1.[Erin Hagens],
-- T-SQL money formatting - format currency
[Erin Hagens Amount] = '$' + convert(VARCHAR,pvt2.[Erin Hagens],1),
[Linda Meisner POs] = pvt1.[Linda Meisner],
[Linda Meisner Amount] = '$' + convert(VARCHAR,pvt2.[Linda Meisner],1),
[Sheela Word POs] = pvt1.[Sheela Word],
[Sheela Word Amount] = '$' + convert(VARCHAR,pvt2.[Sheela Word],1),
[Frank Pellow POs] = pvt1.[Frank Pellow],
[Frank Pellow Amount] = '$' + convert(VARCHAR,pvt2.[Frank Pellow],1),
[Reinout Hillmann POs] = pvt1.[Reinout Hillmann],
[Reinout Hillmann Amount] = '$' + convert(VARCHAR,pvt2.[Reinout Hillmann],1)
FROM cteVendorPO
PIVOT
(COUNT(PurchaseOrderID)
FOR Employee IN ( [Erin Hagens],[Linda Meisner],[Sheela Word],
[Frank Pellow],[Reinout Hillmann] ) ) AS pvt1
INNER JOIN cteVendorTotalDue
PIVOT
(sum(totaldue)
FOR Employee IN ( [Erin Hagens],[Linda Meisner],[Sheela Word],
[Frank Pellow],[Reinout Hillmann] ) ) AS pvt2
ON pvt1.Vendor = pvt2.Vendor
AND pvt1.State = pvt2.State
AND pvt1.City = pvt2.City
ORDER BY pvt1.State,
pvt1.City,
pvt1.Vendor
GO
/* Partial results
| State |
City |
Vendor |
Erin Hagens POs |
Erin Hagens Amount |
Linda Meisner POs |
Linda Meisner Amount |
| AZ |
Lemon Grove |
Greenwood Athletic Company |
5 |
$242,428.44 |
5 |
$242,428.44 |
| CA |
Altadena |
Allenson Cycles |
4 |
$39,105.07 |
5 |
$48,881.33 |
| CA |
Altadena |
Gardner Touring Cycles |
5 |
$2,513.10 |
5 |
$2,513.10 |
| CA |
Berkeley |
Cruger Bike Company |
4 |
$1,527.35 |
5 |
$2,603.57 |
| CA |
Berkeley |
Trikes, Inc. |
4 |
$116,932.32 |
5 |
$146,165.39 |
| CA |
Burbank |
Anderson's Custom Bikes |
5 |
$80,820.11 |
6 |
$96,984.14 |
*/
------------
|
