Change Result Set Columns to Rows

,

I had a developer in my company come to me recently with a unique request.  He asked if there was any way for sql to return a result set that was flopped.  In other words he wanted the rows to be columns and the columns to be rows. 
So that a result set like this:

Col1    Col2    Col3
A    B    C
D    E    F

would look like this:

ColName    Val1    Val2
Col1        A    D
Col2        B    E
Col3        C    F

Seems like this is one of those cool things you might can do with SQL but probably shouldn’t.  Well I couldn’t resist the challenge, so here goes.  If there is some better way to accomplish this I would love to see improvements.

Create Procedure colsToRows 
	@tblName 	varchar(50),	--name of table
	@Cond		varchar(6000)	--limiting condition for record selection
				--any valid where clause with out the where ie id=75 or id between 1 and 5
				--I would recommend that you not return more than 100 rows

AS

declare @colName varchar(50), @colId smallint
declare	@sql varchar(1000)

--Begin selection
select @colName='',@colId=1 

create table #tmp (cName varchar(50))
--add value columns for number of records returned

Select @sql = 'Declare @cols smallint, @sql varchar(7000);Select @cols=count(*) From ' + @tblName
If Len(@cond)>0
	select @sql = @sql + ' Where ' + @cond

select @sql = @sql + ';
	while @cols > 0
	begin
		select @sql = ''alter table #tmp add [Val'' + cast(@cols as varchar) + ''] varchar(50)''
		Exec(@sql)
		select @cols = @cols - 1
	End'

exec( @sql)


set nocount on
while @colname is not null
begin
	select @colname = col_name(object_id(@tblName),@colID)
	Select @colID = @colid + 1
	select @sql = 'declare @retVal varchar(6000);set @retVal = '''';
		update ' + @tblName + ' set @retVal=@retVal + '','' + substring(cast (' + @colname + ' as varchar),1,50)'
	if len(@cond) > 0 
		select @sql = @sql + ' Where ' + @cond
	select @sql = @sql + ';
		select @retval = replace(substring(@retVal,2,len(@retVal)),'','','''''','''''')
		Select @retVal = '''''''' + @retVal + ''''''''
		declare @sql varchar(7000);
		select @sql = ''Insert Into #tmp Values('''''+ @colName + ''''','' + @retVal + '')''
	if len(@sql) > 0 Exec (@sql)
'
	execute (@sql)
end

Select * from #tmp
drop table #tmp
GO

/*
Usage
colsToRows 'SomeTable', 'Some Condition'
*/

Rate

Share

Share

Rate