PIVOT 3D Crosstab with Two-Level Dimension

By Kalman Toth, SQL Server DBA, Business Intelligence Architect

August 12 , 2010

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 hierarchical 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 the dynamic SQL solution, the staff names for the columns would come from a supporting 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:

Related articles:

http://www.sqlusa.com/bestpractices/dynamicsql/

Crosstab queries using PIVOT in SQL Server 2005

Crosstab Pivot-table Workbench