Dynamic Cross Tabs and Pivots, A Different Approach

• Download source - 3.23 KB

Introduction

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

Background

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
GO
--Create test table with four columns
CREATE TABLE dbo.test
(
	Customer_Name varchar(100) null, 
	Product_Name varchar(100) null, 
	Quantity numeric(18,2) null,
	Revenue money null    
)
GO

--fill test table with 100 rows
DECLARE @cnt INT; SET @cnt = 1
WHILE  @cnt <=100 --will loop times
    BEGIN
    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()))

	END

--result 
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
IF EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES
  WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'sp_PivotTable')
  DROP PROCEDURE dbo.sp_PivotTable
GO

--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)
AS
begin
  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
      begin  
      --print 'Parameters OK'
      
      declare @strWhereCondition varchar(1000);
      if @strWhereCnd is not null
        set @strWhereCondition=' where ' + @strWhereCnd;
      else
        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
        begin
          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, _
		len(@strStart)-(charindex('_
		(',@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
            begin
              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
            end

          --second loop end
        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
      end
    else
      begin
        print 'Parameters not provided';
      end

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

end
go

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

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.