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 andthree cursor loops (although I think it could be refactored to two) two cursor loops.
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
/*
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
