tag:blogger.com,1999:blog-29286778.post4253818727026058101..comments2023-07-02T05:53:35.132-04:00Comments on SQL Garbage Collector: Pivot Query Generator for SQL Server 2000Unknownnoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-29286778.post-82571857675452999842012-05-17T18:19:24.457-04:002012-05-17T18:19:24.457-04:00Thank you!!!!!!! You save my life! :)Thank you!!!!!!! You save my life! :)DriJay Rodriguezhttp://www.pisystems.com.brnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-23713436143448797462011-02-04T17:53:15.221-05:002011-02-04T17:53:15.221-05:00Thank you very helpful!
did a small upgrade to or...Thank you very helpful!<br /><br />did a small upgrade to order the pivot column by an index (optional):<br /><br /><br />CREATE proc [GeneratePivotQuery] (<br /> @TableName varchar(100), -- Table to select from<br /> @AggregateFunction varchar(10), -- Aggregate to be done on the value column<br /> @ValueColumn varchar(100),<br /> @PivotColumn varchar(100), -- Column for which the values needs to be transposed<br /> @FilterCondition varchar(1000), -- Filtering to be done on choosing the pivot values<br /> @PivotColumnOrderBy varchar(100),<br /> @OtherColumns varchar(1000)) -- Columns selected other than the pivot columns<br />as<br />begin<br /> declare @List varchar(2000)<br /> set @List = '';<br /><br /> declare @ConcatQuery varchar(4000)<br /> declare @CurColumn varchar(100)<br /> declare @OrderColumn varchar(100)<br /><br /> set @ConcatQuery = 'DECLARE C1 CURSOR global FAST_FORWARD FOR select distinct '+<br /> @PivotColumn + isnull(', ' + @PivotColumnOrderBy,'') + ' from ' + @TableName + isnull(' where ' + @FilterCondition,'') + <br /> isnull(' order by ' + @PivotColumnOrderBy, '')<br /><br /> exec(@ConcatQuery)<br /><br /> open C1<br /><br /> IF(@PivotColumnOrderBy IS NULL)<br /> fetch next from C1 into @CurColumn<br /> ELSE<br /> fetch next from C1 into @CurColumn, @OrderColumn<br /><br /> set @List = @list + @AggregateFunction + '( case when [' + @PivotColumn +<br /> '] = ''' + @CurColumn + ''' then ' +<br /> @ValueColumn + ' else null end ) as [' + @CurColumn + ']'<br /><br /> while (@@fetch_status = 0)<br /> begin<br /><br /> IF(@PivotColumnOrderBy IS NULL)<br /> fetch next from C1 into @CurColumn<br /> ELSE<br /> fetch next from C1 into @CurColumn, @OrderColumn<br /><br /> if(@@fetch_status = 0)<br /> set @List = @list + ',' + char(10) + char(9) + @AggregateFunction + '( case when [' + @PivotColumn +<br /> '] = ''' + @CurColumn + ''' then ' +<br /> @ValueColumn + ' else null end ) as [' + @CurColumn + ']'<br /><br /> end<br /><br /> close c1<br /> deallocate c1<br /><br /> /* this will print the query used to generate the pivoted result */<br /> print '/*query begin*/' + char(10) + char(10) + 'select '+ char(10) + char(9) + @OtherColumns + ',' + char(10)+ char(9) + @List + char(10) + ' from ' + @TableName +<br /> char(10) + ' group by ' + char(10)+ char(9) + @OtherColumns + char(10) + char(10) + '/*query end*/' + replicate(char(10),5)<br /><br /> /* this will generate the result set */<br /> exec ('select ' + @OtherColumns + ',' + @List + ' from ' + @TableName +<br /> ' group by ' + @OtherColumns )<br /><br />endAnonymousnoreply@blogger.com