Paladin Consultants, LLC

Through the 2000 version, Microsoft SQL Server did not provide any support for crosstab queries. In a recent article, Dynamic Crosstabs in SQL Server , I discussed various methods of constructing crosstabs using Transact-SQL. In this article, I present another improved stored procedure which accepts parameters of a base SQL SELECT statement, the name of the PIVOT column, Summary Instruction, GROUPBY field, and Other Columns to create a dynamic crosstab similar to those available in other languages such as Microsoft access, and FoxPro.

The method involves creating a temporary table of the possible values in the crosstab, then creating a dynamic sql statement using those values. The following stored procedure is annotated with examples of its use.

A similar routine written by Jeff Smith is posted on his blog at the sql team website which contains a very lucid and lengthy explanation of the internal process of the stored procedure. In fact, Jeff and the sqlteam have presented a very good cluster of all kinds of good sql server ideas.

create procedure up_CrossTab (@SelectStatement varchar(1000),
@PivotColumn varchar(100),
@Summary varchar(100),
@GroupbyField varchar(100),
@OtherColumns varchar(100) = Null)
AS
/*
Inputs are any 1000 character or less valid SELECT sql statement,
the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. ¹
*/
set nocount on
set ansi_warnings off

declare @Values varchar(8000);
set @Values = '';

set @OtherColumns= isNull(', ' + @OtherColumns,'')
/*
An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. ²
*/
create table #temp (Tempfield varchar(100))

insert into #temp
exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A')

select @Values = @Values + ', ' +
replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' +
Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield )
from #Temp
order by Tempfield

drop table #Temp
/*
Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . ³
*/
exec ( 'select ' + @GroupbyField + @OtherColumns + @Values +
' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)

set nocount off
set ansi_warnings on
GO

/*
¹ Here's an example of calling the procedure, showing the SELECT statement, the Pivot Column, the Summary instruction, and the GroupBy Column:

exec up_CrossTab
'SELECT LastName, OrderDate, Freight FROM northwind..Employees Employees INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'Year(OrderDate)',
'sum(freight)[]',
'LastName'

Note that the OtherColumn parameter was omitted. However, FirstName and Title might be obvious candidates. They must appear in the last parameter, separated by a comma, and also in the SELECT statement parameter.

Output Looks Like this, and represents each order freight costs of each employee in each of the three years of data:

² In our example the different values in the PivotColumn parameter are gathered into the @values variable. Note that we needed to modify that value in our example using the YEAR function. Otherwise, each order date would have it's own column, and might, given enough data, overwhelm the 8000 character limitation of the variable itself.

³ Limiting or qualifying data may be donw either by using a Where clause in the SELECT parameter, or a case or an else clause in the summary parameter, such as SUM( Freight ELSE Null)[].

*/

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a free consultation (973) 635 0080

Computer Consultants: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, LLC Home Page
Computer Consulting: Contact Us