Change Result Set Columns to Rows

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

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'
*/