Atlanta .NET Regular Guys

Pivot Tables in SQL Server

Does anyone use Excel anymore?  Of course they do!  Excel is an awesomly powerful tool that does its job so well I can't name a single competitive product in the spreadsheet category.  I personally love Excel.  It contains loads of powerful automation features, functions, colorful charts, and pivot tables.  What's a pivot table?  Those of you who don't know what a pivot table or cross tab table is, check this out.  Think about a book store.  They might have a list of book titles and a long set of sales numbers for each book.  Now, some pointy-haired manager somewhere at this publishing company is going to say, "I want to know how each of these books have sold, by store".  He demands a list of titles and one aggregate number for sales quantities for each store.  How would you put this together if all you have is a huge list of sales line items?  You'd use a pivot table to get something like this:

Now, Excel has a wizard to make this easy (so does Access, but we won't talk about that), but what if you need to do the same kind of thing in SQL Server?  How would you dynamically generate a table's schema and fill it with data?  With SQL Server 2005 you might write a CLR function to do this for you.  I've seen some fancy VB logic to do the same task, but that was in an external program and that's not very slick, now is it?

I've found some code on the web that allows this very thing - so I took it and adapted the code until I came up with this stored procedure.

/*****************************************************************************/
/*
PROCEDURE: vm_UTY_CrossTab

PURPOSE: ALTER  a pivot or crosstab table out of a select statement

PARAMETERS:  @pSelect - The SELECT statement can be anything, as long as it has
      proper syntax and includes a GROUP BY clause. You can use
      JOINs, but if you use table aliases you should include the
      alias in the summary calculation expression (2).
  @pSumFunc - The summary calculation must have an aggregate function
       like SUM(), AVG(), MIN(), MAX(), etc. You'd have to modify
       the code if you want to use DISTINCT with these functions.
       COUNT(*) won't work, you have to COUNT on a column.
  @pPivot - The pivot column must be in the table (4). You can use an
     expression for the pivot column (a+b, LEFT(FirstName,3), etc.)
     as long as it can be derived from the table listed in (4). A
     cross-tab heading will be created for each distinct value in
     the pivot colum/expression.
  @pTable - This table can be any table in your database, or another database
     if you use the full naming syntax (database.owner.table). Tables
     in a linked server may also work, but I haven't tested this.
     It's possible that a derived table (nested SELECT) can work, but
     I haven't tested this either. You would need to enclose the SELECT
     statement in parentheses, and use a table alias outside these
     parentheses, like this: '(SELECT LastName FROM myTable) AS Surnames'
  @pWhere - This allows you to add a where clause to your crosstab table

COMMENTS: This code has been adapted from this URL: http://www.sqlteam.com/item.asp?ItemID=2955
   The basic use is easy - Select the columns you want to have appear (just the categories)
   The summary column is the value (must be from a table in the select statement) that will
   show up in all the pivot columns.
   The pivot column is the column headers
   The table is where the pivot column should be read from (distinct values)

EXAMPLE: EXECUTE vm_UTY_CrossTab  [SELECT statement],
    [summary calculation],
    [pivot column],
    [table name],
    [filter statement]

 -- Both of these examples execute in the PUBS database.  Add this query to the PUBS DB to test
 EXECUTE vm_UTY_CrossTab 'select title
          from titles
         inner join sales
     on (sales.title_id=titles.title_id)
         group by title',
         'sum(qty)',
         'stor_id',
         'stores'
 
 EXECUTE vm_UTY_CrossTab 'select pub_name,
        count(qty) as orders,
        sum(qty) as total
          from sales
         inner join titles
     on (sales.title_id=titles.title_id)
         right join publishers
     on (publishers.pub_id=titles.pub_id)
         group by pub_name',
         'sum(qty)',
         'type',
         'titles'

CHANGE HISTORY:
 CREATED: Matt Ranlett 30 Sept/05

*/
/*****************************************************************************/
CREATE PROCEDURE vm_UTY_CrossTab
 @pSelect varchar(8000),
 @pSumfunc varchar(100),
 @pPivot varchar(100),
 @pTable varchar(100),
 @pWhere varchar(1000) = ' 1=1 '
AS

 DECLARE @sql varchar(8000), @delim varchar(1)
 SET NOCOUNT ON
 SET ANSI_WARNINGS OFF
 
 /**************************************************************************** 
 create an empty temp table with our pivot value as the only column
 based on our first example, we now have:
 Table ##pivot
 column pivot char 4 NOT NULL -- based on the column pubs.dbo.stores.stor_id
 ****************************************************************************/
 
 EXEC ('SELECT ' + @pPivot + ' AS pivot
   INTO ##pivot
   FROM ' + @pTable + '
  WHERE 1=2')
 /**************************************************************************** 
 insert into our temp table all the unique pivot values from our source table
 based on our first example, we now have:
 6380
 7066
 7067
 7131
 7896
 8042
 ****************************************************************************/
 EXEC ('INSERT INTO ##pivot
  SELECT DISTINCT ' + @pPivot + '
    FROM ' + @pTable + '
   WHERE ' + @pWhere + '
     AND ' + @pPivot + ' Is Not Null')

 /**************************************************************************** 
 append 'END)' to the end of our summary function
 based on our first example, we now have
 @sql = 'sum(qty END)'
 ****************************************************************************/
 SELECT @sql='',  @pSumfunc=stuff(@pSumfunc, len(@pSumfunc), 1, ' END)' )
 
 /**************************************************************************** 
 look for delimiters in our pivot table
 based on our first example, we now have
 @delim = '
 ****************************************************************************/
 SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
 WHEN 0 THEN '' ELSE '''' END
 FROM tempdb.information_schema.columns
 WHERE table_name='##pivot' AND column_name='pivot'
 
 /**************************************************************************** 
 put together our SQL query
 based on our first example, we now have
 @sql =  '6380' = sum( CASE stor_id WHEN '6380' THEN qty END),
  '7066' = sum( CASE stor_id WHEN '7066' THEN qty END),
  '7067' = sum( CASE stor_id WHEN '7067' THEN qty END),
  '7131' = sum( CASE stor_id WHEN '7131' THEN qty END),
  '7896' = sum( CASE stor_id WHEN '7896' THEN qty END),
  '8042' = sum( CASE stor_id WHEN '8042' THEN qty END),
 ****************************************************************************/
 SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
 stuff(@pSumfunc,charindex( '(', @pSumfunc )+1, 0, ' CASE ' + @pPivot + ' WHEN '
 + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
 
 /**************************************************************************** 
 drop our now unnecessary temp table
 ****************************************************************************/
 DROP TABLE ##pivot
 
 /****************************************************************************
 pull off the final comma so we'll have valid SQL in our select list
 based on our first example, we now have
 '6380' = sum( CASE stor_id WHEN '6380' THEN qty END),
 '7066' = sum( CASE stor_id WHEN '7066' THEN qty END),
 '7067' = sum( CASE stor_id WHEN '7067' THEN qty END),
 '7131' = sum( CASE stor_id WHEN '7131' THEN qty END),
 '7896' = sum( CASE stor_id WHEN '7896' THEN qty END),
 '8042' = sum( CASE stor_id WHEN '8042' THEN qty END)
 ****************************************************************************/
 SELECT @sql=left(@sql, len(@sql)-1)

 /**************************************************************************** 
 build up our final query to get our results
 based on our first example, we now have @pSelect =
 select  title,
  '6380' = sum( CASE stor_id WHEN '6380' THEN qty END),
  '7066' = sum( CASE stor_id WHEN '7066' THEN qty END),
  '7067' = sum( CASE stor_id WHEN '7067' THEN qty END),
  '7131' = sum( CASE stor_id WHEN '7131' THEN qty END),
  '7896' = sum( CASE stor_id WHEN '7896' THEN qty END),
  '8042' = sum( CASE stor_id WHEN '8042' THEN qty END)
   from titles
  inner join sales
     on (sales.title_id=titles.title_id)
  group by title
 ****************************************************************************/
 SELECT @pSelect=stuff(@pSelect, charindex(' FROM ', @pSelect)+1, 0, ', ' + @sql + ' ')
 
 /**************************************************************************** 
 execute the query we built
 ****************************************************************************/
 EXEC (@pSelect)
 SET ANSI_WARNINGS ON
 SET NOCOUNT OFF

-- Matt Ranlett