SQLUSA.com
SQL SERVER 2008 GRAND SLAM
FREE TRIAL  CLICK HERE TO ORDER  SEARCH
SQL JOBS SQL Server Training Scripts JOB BANK
  SQL Server 2008 Training Scripts  
  SQL Server 2005 Training Scripts  
News SQL Server Articles SQL Format

 


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

 

Order SQL 2008 GRAND SLAM Today!
SQLUSA.com Home Page
SQL Server Training at www.sqlusa.com.
SQL Server 2008 Video Training at www.sqlusa.com.
SQL Server 2005 Training Videos at www.sqlusa.com.
Microsoft SQL Server 2000 Training Videos at www.sqlusa.com.

FREE SQL & Business Intelligence / OLAP Short Videos on YOUTUBE.com

Microsoft Community Contributor 2011
Invest in Your SUCCESS!

Search SQLUSA FREE SQL Server Articles & FREE T-SQL Scripts


Copyright 2005-2011, SMI Corp. All Rights Reserved.

SQL Server 2012 is a program product of Microsoft Corporation.
SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.