Click here to Skip to main content
15,745,574 members
Articles / Database Development / SQL Server / SQL Server 2008
Posted 13 Sep 2010


25 bookmarked

Dynamic Cross Tabs and Pivots, A Different Approach

Rate me:
Please Sign up or sign in to vote.
4.17/5 (5 votes)
22 Sep 2010CPOL2 min read
In this article, we see a stored procedure to create pivot tables with multiple summary functions from an existing table.


In this article, we see a stored procedure to create pivot tables with multiple summary functions from an existing table.


I’m working as Sr. Software Engineer in a mid-tier company. Sometimes my job requires me to pull some ad-hoc reports from SQL Server. In many of such reports, I had to rotate rows to columns like Pivot Table. Till SQL Server 2000, there was no option to achieve this goal except using case when statements with group by clause, SQL Server 2005 onwards it added Pivot Table functionality in select statement. But that is less customizable and complicated to use (at least for me).

Using the Code

Many people come up with their own versions of dynamic cross tabs and pivot tables. But I can’t find any perfect one which accepts multiple summary functions and returns data with customized column headings, so I decided to write a stored procedure to accomplish this task. To make it more general and to use with any table, let us pass table name, field names, pivot column and summary functions as parameters to the stored procedure.

Now let us create a test table and fill that table with some relevant data. The following script will create a table and fill it with 100 rows of data, which is a dummy sales table. This table has records of ten customers and their sales and revenues of five different products.

--Delete dbo.test table if it already exist
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
  DROP TABLE dbo.test
--Create test table with four columns
	Customer_Name varchar(100) null, 
	Product_Name varchar(100) null, 
	Quantity numeric(18,2) null,
	Revenue money null    

--fill test table with 100 rows
DECLARE @cnt INT; SET @cnt = 1
WHILE  @cnt <=100 --will loop times
    SET @cnt = @cnt + 1
	Insert a single row in test table, we're creating 
	customer name, product name, quantity and revenue 
	randomly using RAND() function
	insert into test (Customer_Name,Product_Name,Quantity,Revenue)
	values ('Customer' + convert(varchar,floor(1+(10)*RAND())), 
	'Product' + convert(varchar,floor(1+(5)*RAND())),
	floor(10+ (100-10)*RAND()), floor(1000 + (10000-1000)*RAND()))


select * from test


Here we have a table with 100 rows. Now we can run a report on this table. We want our report as follows:

  • Distinct Customer Name as rows
  • Product Name with quantity and revenue as columns

Before creating the stored procedure, we’ve to analyze what we want. All we want is an SQL select statement as follows:

Select Customer_Name
  , Sum(case when Product_Name='Product1' then Quantity else 0 end) [Product1 Quantity]
  , Sum(case when Product_Name='Product2' then Quantity else 0 end) [Product2 Quantity]
  , Sum(case when Product_Name='Product3' then Quantity else 0 end) [Product3 Quantity]
  , Sum(case when Product_Name='Product4' then Quantity else 0 end) [Product4 Quantity]
  , Sum(case when Product_Name='Product5' then Quantity else 0 end) [Product5 Quantity]
  , sum(case when Product_Name='Product1' then Revenue else 0 end) [Product1 Revenue]
  , sum(case when Product_Name='Product2' then Revenue else 0 end) [Product2 Revenue]
  , sum(case when Product_Name='Product3' then Revenue else 0 end) [Product3 Revenue]
  , sum(case when Product_Name='Product4' then Revenue else 0 end) [Product4 Revenue]
  , sum(case when Product_Name='Product5' then Revenue else 0 end) [Product5 Revenue]
  from test
group by Customer_Name

But we need to create it dynamically because our Product Name and summary fields are not constant. To create that string, first we need to loop through the summary functions and inside that loop, we’ve to loop through distinct product names. We’ll get comma separated summary functions as one of the arguments of stored procedure and we can retrieve unique product names using the following query:


We can loop through these records using either cursors or convert it to a comma separated string. Let us choose the second option. For that, I used COALESCE function.

The complete script of the stored procedure is as follows:

--Drop Stored Procedure if it already exists
  DROP PROCEDURE dbo.sp_PivotTable

--Create Stored Procedure
CREATE PROCEDURE dbo.sp_PivotTable
  @strTable varchar(200)=null, --Parameters Table Name (Required)
  @strFields varchar(1000)=null, --Comma Separated Field List (Required)
  @strPivotColumn varchar(200)=null, --Pivot Column (Required)
  @strSummaryFn varchar(200)=null, --Comma Separated Summary Functions (Required)
  @strWhereCnd varchar(1000)=null --Where condition (Optional)
  set nocount on

  begin try  
    if @strTable is not null and @strFields is not null and _
	@strPivotColumn is not null and @strSummaryFn is not null
      --print 'Parameters OK'
      declare @strWhereCondition varchar(1000);
      if @strWhereCnd is not null
        set @strWhereCondition=' where ' + @strWhereCnd;
        set @strWhereCondition=' ';

      declare @strFinalSQL varchar(max); --for storing final query
      set @strFinalSQL='Select ' + @strFields + ' ';
      --Splitting Comma Separated Summary Functions
      set @strSummaryFn=@strSummaryFn + ','
      declare @strStart varchar(200)
      declare @strFunction varchar(200)
      declare @strFnFld varchar(200)
      --first loop start
      while charindex(',',@strSummaryFn)>0
          set @strStart=substring(@strSummaryFn,1,charindex(',',@strSummaryFn)-1);
          set @strSummaryFn=substring(@strSummaryFn,charindex_
	(',',@strSummaryFn)+1,len(@strSummaryFn) - charindex(', ',@strSummaryFn));
          set @strFunction=substring(@strStart,1,charindex('_
		(',@strStart)-1); --we'll get function name eg: sum
          set @strFnFld=substring(@strStart,charindex('(',@strStart)+1, _
		(',@strStart)+1)); --we'll get function field eg: quantity

          --Creating dynamic columns
          declare @strResult nvarchar(max), @strXML varchar(max);
          DECLARE @ParmDefinition nvarchar(max);
          --I used COALESCE() function, u can use cursor or any thing 
          --that you find suitable
          set @strResult = 'select @StrOut=COALESCE(@StrOut,'''') + _
		convert(varchar(max),pc) + '','' 
           from (select distinct ' + @strPivotColumn + ' [pc] from '
            + @strTable + @strWhereCondition + ') v order by pc';
          SET @ParmDefinition = '@StrOut varchar(max) OUTPUT';
          EXECUTE sp_executesql @strResult, @ParmDefinition, @StrOut=@strXML OUTPUT;
          declare @strStart2 varchar(max);
          --second loop start
          while charindex(',',@strXML)>0
              set @strStart2=substring(@strXML,1,charindex(',',@strXML)-1); --column name
              set @strXML=substring(@strXML,charindex(',',@strXML)+1, _
		len(@strXML));--reset the string
              set @strFinalSQL=@strFinalSQL + ', ' + @strFunction + _
		'( case when ' + @strPivotColumn +'=''' + @strStart2 + _
		''' then ' + @strFnFld + ' else 0 end) [' + @strStart2 + ' ' _
		+ @strFnFld +'] '; --creates the final string

          --second loop end
        --first loop end
        set @strFinalSQL=@strFinalSQL + ' from ' + @strTable + _
		@strWhereCondition + ' group by ' + @strFields;
        print @strFinalSQL; --to display the final string
        exec(@strFinalSQL); --executes the final query
        print 'Parameters not provided';

  end try
  begin catch
    select isnull(ERROR_MESSAGE(),'') + ' Please contact' AS ErrorMessage;
  end catch


EXECUTE dbo.sp_PivotTable  @strTable='test', @strFields='Customer_Name', 
@strPivotColumn='Product_Name', @strSummaryFn='Sum(Quantity), sum(Revenue)',


Here we have the result. This has Customer Name as rows and Product-wise quantity and revenue as columns (Product1 Quantity, Product2 Quantity Product1 Revenue, Product2 Revenue…). Once you create this stored procedure, you can use this to pull any kind of Pivot reports easily.

If you find it useful or have any better solution, please let me know your valuable feedback. Thank you.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Team Leader
India India
Kannan KR, Team Lead working in a Mobile Application Development Company in Noida, India. Having 8+ years of experience in Web, Mobile and Application Development. Focused on .Net, Java, VB, SQL Server and related technologies as both a career and a personal passion

Comments and Discussions

GeneralAnother Error Pin
twehr22-Sep-10 4:15
twehr22-Sep-10 4:15 
GeneralRe: Another Error Pin
Kannan K R22-Sep-10 20:54
Kannan K R22-Sep-10 20:54 
Thank you very much for informing me the bug, I've updated the sp. Now it will work on numeric fields also.
GeneralRe: Another Error Pin
twehr23-Sep-10 2:33
twehr23-Sep-10 2:33 
GeneralCode creates error Pin
twehr21-Sep-10 2:56
twehr21-Sep-10 2:56 
GeneralRe: Code creates error Pin
Kannan K R21-Sep-10 20:32
Kannan K R21-Sep-10 20:32 
GeneralRe: Code creates error Pin
twehr22-Sep-10 1:44
twehr22-Sep-10 1:44 
Generalput an end to T-SQL Scripting, Please Pin
tortoiseback13-Sep-10 7:00
tortoiseback13-Sep-10 7:00 
GeneralRe: put an end to T-SQL Scripting, Please Pin
Kannan K R13-Sep-10 19:32
Kannan K R13-Sep-10 19:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.