 PIVOT Crosstab Report with Two Measures
By Kalman Toth, Business Intelligence Architect
September 29, 2007
The title reflects a borrowing from the world of data warehousing's star schema. In OLAP cubes there are dimensions and there are measures. In the traditional OLTP and data processing world, there were only columns. However, in the world of On Line Analytical Processing, the “color” of a bicycle is a “dimension” while its “price” is a “measure”. A typical operation is summing of the price by color. At the imaginary company AdventureWorks, employees take purchase orders from vendors such as bicycle dealers or distributors. To tabulate how many PO-s some employees handled, we can use the following query:
-- T-SQL PIVOT operator for crosstab query
USE AdventureWorks;
GO
WITH cteVendorPO
AS (SELECT PurchaseOrderID,
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)
SELECT *
FROM cteVendorPO
PIVOT
(COUNT(PurchaseOrderID)
FOR Employee IN ( [Erin Hagens],[Linda Meisner],[Sheela Word],
[Frank Pellow],[Reinout Hillmann] ) ) AS pvt
ORDER BY pvt.Vendor
GO
In this crosstab report, the measure is just the count of purchase orders while the dimensions are vendors and the listed employees handling the PO-s. Here is the partial result:
Vendor |
Erin Hagens |
Linda Meisner |
Sheela Word |
Advanced Bicycles |
5 |
5 |
2 |
Allenson Cycles |
4 |
5 |
2 |
American Bicycles and Wheels |
4 |
5 |
2 |
American Bikes |
4 |
5 |
1 |
To make this report more meaningful, we want to add the sum of TotalDue on each purchase order, after all one PO maybe worth $50, another $50,000. To do this, we need a second PIVOT which uses SUM(TotalDue) for aggregation instead of COUNT(PurchaseOrderID). Finally, we have to JOIN the 2 crosstabs: we join the rows on vendors and we join the columns by listing count and totaldue columns for the employee next to each other. We blank out the name of employee in the TotalDue column to make the report more business like. Here is the query:
-- MSSQL PIVOT with two measures - crosstab query
USE AdventureWorks;
GO
WITH cteVendorPO
AS (SELECT PurchaseOrderID,
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),
cteVendorTotalDue
AS (SELECT Employee = FirstName + ' ' + LastName,
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)
SELECT pvt1.Vendor,
pvt1.[Erin Hagens],
[''] = '$' + convert(VARCHAR,pvt2.[Erin Hagens],1),
pvt1.[Linda Meisner],
[''] = '$' + convert(VARCHAR,pvt2.[Linda Meisner],1),
pvt1.[Sheela Word],
[''] = '$' + convert(VARCHAR,pvt2.[Sheela Word],1),
pvt1.[Frank Pellow],
[''] = '$' + convert(VARCHAR,pvt2.[Frank Pellow],1),
pvt1.[Reinout Hillmann],
[''] = '$' + 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
ORDER BY pvt1.Vendor
GO
Here are the partial results:
Vendor |
Erin Hagens |
'' |
Linda Meisner |
'' |
Advanced Bicycles |
5 |
$2,460.82 |
5 |
$3,662.90 |
Allenson Cycles |
4 |
$39,105.07 |
5 |
$48,881.33 |
American Bicycles and Wheels |
4 |
$756.16 |
5 |
$945.20 |
American Bikes |
4 |
$90,156.07 |
5 |
$112,695.08 |
|
