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))

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