PIVOT 3D Crosstab with Two-Level Dimension

By Kalman Toth, Business Intelligence Architect

June 24 , 2008

This article expands upon a crosstab query in a previous article. Two measures constitute the data for the report: the number of purchase orders and the total dollar amount of POs. Across the columns the employees are listed who handled the POs (one dimension). The rows have 2 dimensions: country-state (2-level dimension) and vendor. While the resulting power report is absolutely marvelous, when we look at the query we see excessive hard-wiring: namely the columns are hard-wired with the staff names. This is due to the SQL Server 2005 implementation of PIVOT which is not dynamic, it requires a hard-wired literal list for columns. Currently the only way to circumvent is to envelope the query with dynamic SQL. In dynamic SQL, the staff names for the columns would come from an outside query. Try it! You will have fun. The dynamic SQL edition of this crosstab report query will be pretty awesome looking.

This is the entire query with double CTEs (Common Table Expression) for structured coding:

USE AdventureWorks;

GO

WITH cteVendorPO

AS

(

SELECT PurchaseOrderID, Country=cr.Name, State=StateProvinceCode,

Employee=FirstName+' '+LastName,

Vendor = v.Name

FROM Purchasing.PurchaseOrderHeader poh

JOIN HumanResources.Employee e

ON poh.EmployeeID = e.EmployeeID

JOIN Person.Contact c

ON e.ContactID = c.ContactID

JOIN Purchasing.Vendor v

ON v.VendorID = poh.VendorID

JOIN Purchasing.VendorAddress va

ON v.VendorID=va.VendorID

JOIN Person.Address a

ON a.AddressID = va.AddressID

JOIN Person.StateProvince sp

ON sp.StateProvinceID=a.StateProvinceID

JOIN Person.CountryRegion cr

ON cr.CountryRegionCode = sp.CountryRegionCode

),

cteVendorTotalDue

AS

(

SELECT Employee=FirstName+' '+LastName, Country=cr.Name, State=StateProvinceCode,

Vendor = v.Name, TotalDue

FROM Purchasing.PurchaseOrderHeader poh

JOIN HumanResources.Employee e

ON poh.EmployeeID = e.EmployeeID

JOIN Person.Contact c

ON e.ContactID = c.ContactID

JOIN Purchasing.Vendor v

ON v.VendorID = poh.VendorID

JOIN Purchasing.VendorAddress va

ON v.VendorID=va.VendorID

JOIN Person.Address a

ON a.AddressID = va.AddressID

JOIN Person.StateProvince sp

ON sp.StateProvinceID=a.StateProvinceID

JOIN Person.CountryRegion cr

ON cr.CountryRegionCode = sp.CountryRegionCode

)

SELECT pvt1.Country, pvt1.State, pvt1.Vendor,

[Erin Hagens POs]=pvt1.[Erin Hagens], [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

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.Country=pvt2.Country

ORDER BY pvt1.Country, pvt1.State, pvt1.Vendor

Here is the partial result: