Technical Article

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
@Condvarchar(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


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
select @sql = ''alter table #tmp add [Val'' + cast(@cols as varchar) + ''] varchar(50)''
select @cols = @cols - 1

exec( @sql)

set nocount on
while @colname is not null
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)

Select * from #tmp
drop table #tmp

colsToRows 'SomeTable', 'Some Condition'