DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

 

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:

Country State Vendor Erin Hagens POs Erin Hagens Amount Linda Meisner POs Linda Meisner Amount
United States AZ  Greenwood Athletic Company 5 $242,428.44 5 $242,428.44
United States CA  Allenson Cycles 4 $39,105.07 5 $48,881.33
United States CA  American Bicycles and Wheels 4 $756.16 5 $945.20
United States CA  American Bikes 4 $90,156.07 5 $112,695.08
United States CA  Anderson's Custom Bikes 5 $80,820.11 6 $96,984.14
United States CA  Bloomington Multisport 4 $646.58 5 $808.23
United States CA  Capital Road Cycles 5 $75,523.57 5 $75,523.57
United States CA  Chicago City Saddles 4 $211,982.90 5 $168,544.88
United States CA  Chicago Rent-All 4 $2,439.31 5 $3,049.14
United States CA  Comfort Road Bicycles 5 $155,099.32 5 $155,099.32
United States CA  Consumer Cycles 4 $264.95 6 $397.43
United States CA  Continental Pro Cycles 5 $1,691.92 5 $1,418.96

Related articles:

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

Crosstab queries using PIVOT in SQL Server 2005

Crosstab Pivot-table Workbench

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE