Thank you very helpful! did a small upgrade to or...

2011-02-04T17:53:15.221-05:00

Thank you very helpful!

did a small upgrade to order the pivot column by an index (optional):

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
@PivotColumnOrderBy varchar(100),
@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)
declare @OrderColumn varchar(100)

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

exec(@ConcatQuery)

open C1

IF(@PivotColumnOrderBy IS NULL)
fetch next from C1 into @CurColumn
ELSE
fetch next from C1 into @CurColumn, @OrderColumn

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

while (@@fetch_status = 0)
begin

IF(@PivotColumnOrderBy IS NULL)
fetch next from C1 into @CurColumn
ELSE
fetch next from C1 into @CurColumn, @OrderColumn

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

Comments on SQL Garbage Collector: Pivot Query Generator for SQL Server 2000

Thank you very helpful! did a small upgrade to or...