A few weeks ago we started working on a statistical analysis piece of our produciton managment application. If you don’t know, I am the Information Services Director for a small manufacturing company, but I will write more on that later.

We recently started accumulating quality assurance information digitally inside our software, which begs the age old question: “What good is data if you can’t get to it?”. Enter statistical analysis, what we want to do is be able to take all of the possible issues that can happen and cross reference it against one or more manufacturing jobs. So this would be a piece of cake in excel, but wait we are not using excel for this and discourage our users from doing so.

So we are poking around on the net and find a couple of leads that lead us towards having the database server do the crosstab for us. So one thing I found is that SQL Server 2005 has some built in Pivot functions that don’t work very well either according to all that I read.  So what I am posting today is a stored procedure that creates a crosstab result set. I will post the stored procedure and then an example of how to call it. This code originally came from Rob Volk posted over at: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables and I have modified it to work in SQL Server 2005 and allow a where clause at runtime. This is a very fast and lightweight stored procedure that works amazingly well.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[crosstab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@whereSQL varchar(5000)
AS DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF</code><code>EXEC ('SELECT ' + @pivot + ' AS pivotcol INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null ' + @whereSQL)</code><code>SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )</code><code>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='pivotcol'</code><code>SELECT @sql=@sql + '''' + convert(varchar(100), pivotcol) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivotcol) + @delim + ' THEN ' ) + ', ' FROM ##pivot</code><code>DROP TABLE ##pivot</code><code>SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')</code><code>EXEC (@select)
SET ANSI_WARNINGS ON

In order to use this query you have to use the following syntax. And on a side note, it appears that in SQL Server 2005 you have to keep this all on one line, nice huh?

I am going to give a bit of explanation on these now:

1. The SELECT statement can be anything as long as it is a a valid SELECT statement (i.e. proper syntax, etc…)
2. The summary calcualation must have an aggregate function such as (AVG, SUM, MIN, MAX, etc.. ) You can use COUNT but it must be on a column
3. The pivot column must be in the table that is specified in 4. A cross tab heading will be created for each distinct value in the pivot column.
4. This need to be the table name that contains your pivot data. It could be in another database so long as you specify that with the full syntax for naming.
5. This is the where clause, you will want to set this up as follows:

[sql]
DECLARE @whereSQL varchar(5000);
SELECT @whereSQL = ‘AND COLUMN in (select COLUMN from TABLEA where DATE < GETDATE())’[/sql]

Once you have supplied all the paramters you will have a nice little crosstab result set.