Pivot Query Generator for SQL Server 2000

This post is written with reference to the post Pivot Query Generator for SQL Server 2005. Please read through the post to get the context and the data setup before you read any further.

Now, that you have read through the other post, you will realise that in SQL Server 2005, we had the PIVOT operator to generate the crosstab reports. But in case of SQL Server 2000, we will have to use an indirect approach to generate the cross-tab. Here is the SQL Server 2000 counter part of the Pivot Query Generator of SQL Server 2005.


This SP is still in a rudimentary stage and does not handle join conditions. You may use it to see how to generate cross-tab reports and extend it to your requirements. Please feel free to modify the SP to suit to your requirements. And if you can see a way by which it can be improved, leave a comment and I will try to update it if possible.

Though, the implementation of this SP is different from the one written for SQL Server 2005, the end result should be the same. And you can get the query generated from the messages tab of your query analyzer.

Here is the stored procedure definition:

create proc GeneratePivotQuery (
@TableName varchar(100), -- Table to select from
@AggregateFunction varchar(10), -- Aggregate to be done on the value column
@ValueColumn varchar(100),
@PivotColumn varchar(100), -- Column for which the values needs to be transposed
@FilterCondition varchar(1000), -- Filtering to be done on choosing the pivot values
@OtherColumns varchar(1000)) -- Columns selected other than the pivot columns
as
begin
declare @List varchar(2000)
set @List = '';

declare @ConcatQuery varchar(4000)
declare @CurColumn varchar(100)

set @ConcatQuery = 'DECLARE C1 CURSOR global FAST_FORWARD FOR select distinct '+
@PivotColumn + ' from ' + @TableName + isnull(' where ' + @FilterCondition,'')

exec(@ConcatQuery)

open C1

fetch next from C1 into @CurColumn

set @List = @list + @AggregateFunction + '( case when [' + @PivotColumn +
'] = ''' + @CurColumn + ''' then ' +
@ValueColumn + ' else null end ) as [' + @CurColumn + ']'

while (@@fetch_status = 0)
begin

fetch next from C1 into @CurColumn
if(@@fetch_status = 0)
set @List = @list + ',' + char(10) + char(9) + @AggregateFunction + '( case when [' + @PivotColumn +
'] = ''' + @CurColumn + ''' then ' +
@ValueColumn + ' else null end ) as [' + @CurColumn + ']'

end

close c1
deallocate c1

/* this will print the query used to generate the pivoted result */
print '/*query begin*/' + char(10) + char(10) + 'select '+ char(10) + char(9) + @OtherColumns + ',' + char(10)+ char(9) + @List + char(10) + ' from ' + @TableName +
char(10) + ' group by ' + char(10)+ char(9) + @OtherColumns + char(10) + char(10) + '/*query end*/' + replicate(char(10),5)

/* this will generate the result set */
exec ('select ' + @OtherColumns + ',' + @List + ' from ' + @TableName +
' group by ' + @OtherColumns )

end

Here is the call to the stored procedure, that will pivot those values in city2 where city2 is between 'B' and 'D' and display the result. You can get the query, used to generate the result, from the messages tab.

DECLARE @TableName varchar(100)
DECLARE @AggregateFunction varchar(10)
DECLARE @ValueColumn varchar(100)
DECLARE @PivotColumn varchar(100)
DECLARE @FilterCondition varchar(1000)
DECLARE @OtherColumns varchar(1000)

SELECT @TableName = 'distance_tbl'
SELECT @AggregateFunction = 'sum'
SELECT @ValueColumn = 'distance'
SELECT @PivotColumn = 'city2'
SELECT @FilterCondition = 'city2 between ''B'' and ''D'''
SELECT @OtherColumns = 'city1'

EXEC [dbo].[GeneratePivotQuery]
@TableName,
@AggregateFunction,
@ValueColumn,
@PivotColumn,
@FilterCondition,
@OtherColumns