T-SQL: Rotate Columns into Rows, Take 2

I posted some code yesterday that rotates one row of sql results into a vertical name/value result set.
It serves its purpose. But it was weak: you had to pass it straight forward select statements that return one result, and you had to piece together the arguments, rather that just giving it a SQL statement and letting it do its job.

The code below is an improvement.

It will take arbitrary well formed sql statements of any sort and invert them into name value pairs. It is not intended to be high performance code. And it shamelessly uses a global temp table and three cursor loops (although I think it could be refactored to two) two cursor loops.

/*
Rotate 'select top 1 * from Production.Product'
*/
alter procedure Rotate(@sql nvarchar(1000)) as

set nocount on

set @sql = replace(@sql, ' from ', ' into ##t from ')

exec sp_executesql  @sql

declare @columnName varchar(25)
declare @declareSql varchar(1000) ; set @declareSql = ''
declare @cursorSql varchar(5000) ; set @cursorSql  = ''
declare @insertSql varchar(5000) ; set @insertSql  = ''
declare c2 cursor dynamic for select sc.name from tempdb..sysobjects so 
	inner join tempdb..syscolumns sc on sc.id = so.id 
	where so.name like '##t%'
open c2

fetch next from c2 into @columnName
while @@FETCH_STATUS = 0 begin	
	set @declareSql = @declareSql  + 'declare @' + @columnName + ' varchar(1000)
'
	set @cursorSql = @cursorSql + '@' + @columnName + ','
	set @insertSql = @insertSql + ';insert #namevalue values(''' + @columnName + ''', @' + @ColumnName + ')'

	fetch next from c2 into @columnName
end

set @cursorSql = substring(@cursorSql, 0, len(@cursorSql))
declare @dynamicSql nvarchar(max)
set @dynamicSql = @declareSql + N'
	create table #nameValue (Name varchar(255), Value varchar(1000))	
	declare c  cursor for select * from ##t; open c
	fetch next from c into ' + @cursorSql + '
	
	while @@FETCH_STATUS = 0 begin
		fetch next from c into ' + @cursorSql + '
			insert #namevalue values(''--- Name ---'', ''--- Value ---'')' +
			@insertSql + '
	end
	select * from #nameValue
	drop table #nameValue
	close c;deallocate c'


print @dynamicSql
exec sp_executesql  @dynamicSql


close c2; deallocate c2
drop table ##t


        
| Comments (0)TrackBacks (0)

0 TrackBacks

Listed below are links to blogs that reference this entry: T-SQL: Rotate Columns into Rows, Take 2.

TrackBack URL for this entry: http://www.rootsilver.com/mt-tb.cgi/84

Leave a comment