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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create crosstab report using PIVOT?

Execute the following Microsoft SQL Server T-SQL example scripts in Query Editor to create crosstab reports using the PIVOT operator and the CASE function.

-- SQL pivot query - sql pivot crosstab - rows to columns sql - sql crosstab pivot

SELECT 'Average Cost' AS CostByDaysToManufacture,

      convert(varchar,[0],1) [0 day],  convert(varchar,[1],1) [1 day],

      convert(varchar,[2],1) [2 days], convert(varchar,[3],1) [3 days],

      convert(varchar,[4],1) [4 days], convert(varchar,[5],1) [5 days]

FROM

(SELECT DaysToManufacture, StandardCost

    FROM Production.Product) AS VerticalData

PIVOT

(

      AVG(StandardCost)

      FOR DaysToManufacture IN ([0], [1], [2], [3], [4], [5])

) AS pvt;

/*

CostByDaysToManufacture 0 day 1 day       2 days      3 days      4 days      5 days

Average Cost            5.09  223.88      359.11      NULL        949.41      NULL

*/

------------

 

The first crosstab (cross tabulation report) set of queries is on purchase orders issued by purchasing staff to various vendors.The T-SQL PIVOT operator sets data in rows into columns. The crosstab cell contains a measure such as the COUNT aggregate of items puchased by purchasing staff from a vendor or the SUM of purchase order totals by staff by vendor.

Notice the application of the new SQL Server 2005 PIVOT operator in the second query. The first query is used to determine the staff list for the PIVOT.

-- SQL Server pivot - SQL Server crosstab query

USE AdventureWorks

-- Use the following query to create the IN list for the PIVOT

-- The list will become the column headers in the results

-- The entire list can be used for the pivot or a subset selected

-- SQL cte - common table expression - SQL inner join

-- SQL create comma-delimited list - SQL for xml path

DECLARE @PivotList varchar(1024)

SET @PivotList='';

WITH cteEmployeePO(Staff)

     AS (SELECT left(rtrim(FirstName) + ' ' + rtrim(LastName), 30)

         FROM   Purchasing.PurchaseOrderHeader poh

                INNER JOIN Purchasing.Vendor v

                  ON poh.VendorID = v.VendorID

                INNER JOIN HumanResources.Employee e

                  ON poh.EmployeeID = e.EmployeeID

                INNER JOIN Person.Contact c

                  ON e.ContactID = c.ContactID)

SELECT @PivotList = STUFF((SELECT ', '+QUOTENAME(Staff) AS [text()]

FROM (SELECT DISTINCT Staff from cteEmployeePO ) cte

FOR XML PATH ('')),1,1,'');

 

SELECT @PivotList

GO

 

/* [Annette Hill], [Arvind Rao], [Ben Miller], [Eric Kurjan], [Erin Hagens],

 [Frank Pellow], [Fukiko Ogisu], [Gordon Hee], [Linda Meisner],

[Mikael Sandberg], [Reinout Hillmann], [Sheela Word]

*/

 

 

-- SQL pivot query

;WITH cteEmployeePO(Staff,Dealer,PurchaseOrderID)

     AS (SELECT left(rtrim(FirstName) + ' ' + rtrim(LastName), 30),

                left(v.Name,40),

                PurchaseOrderID

         FROM   Purchasing.PurchaseOrderHeader poh

                INNER JOIN Purchasing.Vendor v

                  ON poh.VendorID = v.VendorID

                INNER JOIN HumanResources.Employee e

                  ON poh.EmployeeID = e.EmployeeID

                INNER JOIN Person.Contact c

                  ON e.ContactID = c.ContactID)

SELECT pvt.*

FROM     cteEmployeePO cte

         PIVOT

         (COUNT(PurchaseOrderID)

          FOR Staff IN ( [Annette Hill],[Arvind Rao],[Ben Miller],

          [Eric Kurjan],[Erin Hagens],[Frank Pellow],[Fukiko Ogisu],

          [Gordon Hee],[Linda Meisner],[Mikael Sandberg],

          [Reinout Hillmann],[Sheela Word] ) ) AS pvt

ORDER BY pvt.Dealer

/* Partial results

   SQL crosstab report POs placed by purchasing staff to vendors

  

Dealer                        Annette Hill      Arvind Rao  Ben Miller 

Advanced Bicycles             5                 3           4          

Allenson Cycles               4                 3           5          

American Bicycles and Wheels  4                 1           5    

American Bikes                5                 2           4    

Anderson's Custom Bikes       5                 2           4    

*/

-- ALTERNATE query to get the IN list for the PIVOT

-- SQL cte

-- SQL create comma-limited list

-- SQL inner join query

DECLARE @PivotList varchar(1024)

SET @PivotList='';

WITH cteEmployeePO(Staff,Dealer,PurchaseOrderID)

     AS (SELECT left(rtrim(FirstName) + ' ' + rtrim(LastName), 30),

                left(v.Name,40),

                PurchaseOrderID

         FROM   Purchasing.PurchaseOrderHeader poh

                INNER JOIN Purchasing.Vendor v

                  ON poh.VendorID = v.VendorID

                INNER JOIN HumanResources.Employee e

                  ON poh.EmployeeID = e.EmployeeID

                INNER JOIN Person.Contact c

                  ON e.ContactID = c.ContactID)

SELECT @PivotList=@PivotList+'['+Staff+'], '

FROM (SELECT DISTINCT Staff from cteEmployeePO ) cte

SET @PivotList = LEFT(@PivotList, len(@PivotList)-1)

SELECT @PivotList

GO

/* Result

[Gordon Hee], [Arvind Rao], [Fukiko Ogisu], [Ben Miller], [Eric Kurjan],

[Sheela Word], [Erin Hagens], [Frank Pellow], [Reinout Hillmann],

[Linda Meisner], [Annette Hill], [Mikael Sandberg]

*/

 

 

T-SQL Crosstab queries on list prices (ListPrice) by Color - over rows - and product Category - across columns.

Notice the variations for the list price and the application of PIVOT or CASE.

 

-- SQL pivoting rows into columns

-- SQL pivot example

USE tempdb;

 

-- Select into create table for test dataset

SELECT Color,

       Category = c.Name,

       ListPrice

INTO   ProdCat

FROM   AdventureWorks.Production.Product p

       INNER JOIN AdventureWorks.Production.ProductSubCategory sc

         ON p.ProductSubCategoryID = sc.ProductSubCategoryID

       INNER JOIN AdventureWorks.Production.ProductCategory c

         ON sc.ProductCategoryID = c.ProductCategoryID

WHERE  ListPrice > 0

       AND Color IS NOT NULL

       AND p.ProductSubCategoryID IS NOT NULL

 

-- SELECT * FROM ProdCat

-- Get column list for pivoting

SELECT DISTINCT Category

FROM   ProdCat

 

/*

Category

Accessories

Bikes

Clothing

Components

*/

 

-- SQL pivot rows to columns

-- MIN aggregate function is used since there can be more than one row

-- with same color and category

-- SQL pivot crosstab query

SELECT   pvt.*

FROM     ProdCat pc

         PIVOT

         (Min(ListPrice)

          FOR Category IN

         ( [Accessories],[Bikes],[Clothing],[Components] ) ) AS pvt

ORDER BY pvt.Color

 

-- SQL case function pivot

-- SQL case function crosstab

SELECT Color,

       Min(CASE

             WHEN Category = 'Accessories' THEN ListPrice

             ELSE NULL

           END) AS Accessories,

       Min(CASE

             WHEN Category = 'Bikes' THEN ListPrice

             ELSE NULL

           END) AS Bikes,

       Min(CASE

             WHEN Category = 'Clothing' THEN ListPrice

             ELSE NULL

           END) AS Clothing,

       Min(CASE

             WHEN Category = 'Components' THEN ListPrice

             ELSE NULL

           END) AS Components

FROM     ProdCat

GROUP BY Color

ORDER BY Color

 

GO

 

/* Results

 

Color             Accessories  Bikes      Clothing    Components

Black             34.99       539.99      24.49       60.745

Blue              34.99       742.35      63.50       333.42

Grey              125.00      NULL        NULL        NULL

Multi             NULL        NULL        8.99        NULL

Red               34.99       782.99      NULL        337.22

Silver            54.99       564.99      NULL        20.24

Silver/Black      NULL        NULL        NULL        40.49

White             NULL        NULL        8.99        NULL

Yellow            NULL        742.35      53.99       333.42

*/

 

-- MAX ListPrice version

-- SQL pivot query

SELECT   pvt.*

FROM     ProdCat pc

         PIVOT

         (Max(ListPrice)

          FOR Category IN

         ( [Accessories],[Bikes],[Clothing],[Components] ) ) AS pvt

ORDER BY pvt.Color

 

-- SQL case

SELECT Color,

       Max(CASE

             WHEN Category = 'Accessories' THEN ListPrice

             ELSE NULL

           END) AS Accessories,

       Max(CASE

             WHEN Category = 'Bikes' THEN ListPrice

             ELSE NULL

           END) AS Bikes,

       Max(CASE

             WHEN Category = 'Clothing' THEN ListPrice

             ELSE NULL

           END) AS Clothing,

       Max(CASE

             WHEN Category = 'Components' THEN ListPrice

             ELSE NULL

           END) AS Components

FROM     ProdCat

GROUP BY Color

ORDER BY Color

 

GO

 

/* Results

Color             Accessories  Bikes      Clothing    Components

Black             34.99       3374.99     74.99       1431.50

Blue              34.99       2384.07     63.50       1003.91

Grey              125.00      NULL        NULL        NULL

Multi             NULL        NULL        89.99       NULL

Red               34.99       3578.27     NULL        1431.50

Silver            54.99       3399.99     NULL        1364.50

Silver/Black      NULL        NULL        NULL        80.99

White             NULL        NULL        9.50        NULL

Yellow            NULL        2384.07     53.99       1003.91

*/

 

-- ALL ListPrice-s comma-limited list version

-- SQL Sever pivot rows to columns

-- SQL for xml path list

SELECT Color,

       Stuff((SELECT DISTINCT ', ' + Convert(VARCHAR,ListPrice) AS [text()]

              FROM   ProdCat pc1

              WHERE  pc1.Color = pc.Color

                     AND Category = 'Accessories'

              FOR XML PATH ('')),1,1,'') AS Accessories,

       Stuff((SELECT DISTINCT ', ' + Convert(VARCHAR,ListPrice) AS [text()]

              FROM   ProdCat pc1

              WHERE  pc1.Color = pc.Color

                     AND Category = 'Bikes'

              FOR XML PATH ('')),1,1,'') AS Bikes,

       Stuff((SELECT DISTINCT ', ' + Convert(VARCHAR,ListPrice) AS [text()]

              FROM   ProdCat pc1

              WHERE  pc1.Color = pc.Color

                     AND Category = 'Clothing'

              FOR XML PATH ('')),1,1,'') AS Clothing,

       Stuff((SELECT DISTINCT ', ' + Convert(VARCHAR,ListPrice) AS [text()]

              FROM   ProdCat pc1

              WHERE  pc1.Color = pc.Color

                     AND Category = 'Components'

              FOR XML PATH ('')),1,1,'') AS Components

FROM     ProdCat pc

GROUP BY Color

ORDER BY Color

 

GO

 

/* Partial results

 

Color       Accessories       Bikes

Black       34.99             1079.99, 2294.99, 2443.35, 3374.99, 539.99, 782.99

Blue        34.99             1214.85, 2384.07, 742.35

Grey        125.00            NULL

Multi       NULL              NULL

Red         34.99             1457.99, 2443.35, 3578.27, 782.99

Silver      54.99             2319.99, 3399.99, 564.99, 769.49

Silver/Black  NULL            NULL

White       NULL              NULL

Yellow      NULL              1120.49, 1700.99, 2384.07, 742.35

*/

 

-- Cleanup

DROP TABLE ProdCat

 

GO

-- 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

Related articles:

Using PIVOT and UNPIVOT

Creating cross tab queries and pivot tables in SQL

 

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