Crosstab queries using PIVOT in SQL Server

By: Greg Robidoux   |   Comments (68)   |   Related: 1 | 2 | More > Query Optimization

Problem

The need to create cross tab queries like you would do in Excel is something that could be handy with SQL Server data. In this article we look at using PIVOT to create cross tab results.

Solution

We will use the SQL Server PIVOT to create cross-tab results and show an example.

Let's say we have data in a table that looks like this:

And what we want to do is to rearrange the output to look like below. First you have the data rows such as SalesPerson, and then columns for each Product with the summarized SalesAmount value for each cross section. 

Let's create a sample table and insert some data that we can use with a PIVOT query.

CREATE TABLE ProductSales
( SalesPerson varchar(20),
  Product varchar(20),
  SalesAmount money )
GO

INSERT INTO ProductSales
SELECT 'Bob','Pickles',100.00
UNION
SELECT 'Sue','Oranges',50.00
UNION
SELECT 'Bob','Pickles',25.00
UNION
SELECT 'Bob','Oranges',300.00
UNION
SELECT 'Sue','Oranges',500.00  
GO

Here is a simple PIVOT query that allows us to pull the cross-tab results as shown above.

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM 
   ( SELECT SalesPerson, Product, SalesAmount
     FROM ProductSales 
   ) ps
PIVOT
   ( SUM (SalesAmount)
     FOR Product IN ( [Oranges], [Pickles])
   ) AS pvt

So how does this work?

There are three pieces that need to be understood in order to construct the query.

• (1) The SELECT statement
  • SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
  • This portion of the query selects the three columns for the final result set (SalesPerson, Oranges, Pickles)

   

• (2) The query that pulls the raw data to be prepared
  • (SELECT SalesPerson, Product, SalesAmount FROM ProductSales) ps
  • This query pulls all the rows of data that we need to create the cross-tab results.  The (ps) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.

   

• (3) The PIVOT expression
  • PIVOT (SUM (SalesAmount) FOR Product IN ( [Oranges], [Pickles]) ) AS pvt
  • This query does the actual summarization and puts the results into a temporary table called pvt

Another key thing to notice in here is the use of the square brackets [ ] around the column names in both the SELECT in part (1) and the IN in part (3).  These are key, because the pivot operation is treating the values in these columns as column names and this is how the breaking and grouping is done to display the data.

Next Steps

• Take a look at the new PIVOT option that SQL Server offers to see how you can use this
• Try to write some more complex queries to take advantage of this new option.
• Also take a look at this tip that dynamically creates the PIVOT query instead of having to hard code the query.

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

View all my tips