Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Resources Links And Tools
Print E-mail

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. 1
*/

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. 2
*/

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 . 3
*/

exec ( 'select ' + @GroupbyField + @OtherColumns + @Values +
' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)

set nocount off
set ansi_warnings on
GO

/*
1 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:

Lastname 1996 1997 1998
Buchanan 1365.3700
1184.7500 1368.5900
Callahan 1258.4000 2935.2800 3294.2000
Davolio 1871.0400 4584.4700 2381.1300
Dodsworth 532.8400 1046.0900 1747.3300
Fuller 973.1800 3796.4100 3926.8200
King 664.3200 3240.6600 2760.4600
Leverling 880.0300 6918.3400 3086.3700
Peacock 1968.5900 6648.6000 2728.9500
Suyama 766.1000 2114.1700 900.2000

2 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.

3 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

 

 
Bookmark and Share