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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES

Microsoft SQL Server 2005 Articles

 

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

 

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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