Have you ever need to transpose a whole table? Mostly you need not, neither did I. We might need some kind of cross-tab resultsets mainly while doing Reporting and reporting tools are well versed for handling this requirements and that should be used. But today, I have show similar question at MSDN forum, so created procedure to meet those requirements thinking it might be helpful to somebody else as well.

Below procedure which takes the tablename as input and completely transpose the table, procedure also has an optional where condition parameter which can be used to limit number of rows transposed.

if object_id('dbo.proc_TransposeTable') is not null
    drop procedure dbo.proc_TransposeTable
go
/**********************************************************************************
Procedure Name  : proc_TransposeTable
Created By      : Chintak Chhapia
Created on      : 7/25/2012    
Sample Call     : exec proc_TransposeTable N'HumanResources.Employee',N'employeeID < 300'
Parameter       : @tableName 
                  @whereCondition - optional
**********************************************************************************/
Create Procedure dbo.proc_TransposeTable
    @tableName nvarchar(128)
    , @whereCondition nvarchar(128) = NULL
as
    set nocount on;
    
    declare @PivotColumnNames nvarchar(max)
    declare @dynamicSQL nvarchar(max) 
    declare @columnName nvarchar(128)
    declare @rows int
    declare @type int
    
    select @dynamicSQL = N'select @rowsin = count(*) from ' + @tableName + char(10)
    
    if (@whereCondition is not null)
    begin
        select @dynamicSQL = @dynamicSQL + ' where ' + @whereCondition
    end
    
    exec sp_executeSQL @dynamicSQL,N'@rowsin int output',@rowsin =  @rows output
    
    ;  with  l0 as(select 1 as c   
                    union all   
                    select 1)  
    ,  l1 as(select 1 as c from l0 as a, l0 as b)  
    ,  l2 as (select 1 as c from l1 as a, l1 as b)  
    ,  l3 as (select 1 as c from l2 as a, l2 as b)  
    ,  l4 as (select 1 as c from l3 as a, l3 as b)  
    ,  l5 as(select 1 as c from l4 as a, l4 as b)  
    ,  nums as(select row_number() over(order by c) as n from l5)   
    ,  PivotColumnNames
        as
        (
            select 'r' + cast(n as nvarchar(10)) as rn 
            from nums 
            where n <=  @rows
        )        
    
    select @PivotColumnNames = coalesce( @PivotColumnNames+',','') + quotename(rn,']')
    from PivotColumnNames
    
    select @dynamicSQL = ';with CTE' + char(10)
    select @dynamicSQL = @dynamicSQL + 'as' + char(10)
    select @dynamicSQL = @dynamicSQL + '( ' + char(10)
    select @dynamicSQL = @dynamicSQL + 'select *  ' + char(10)
    select @dynamicSQL = @dynamicSQL + ', row_number() over (order by (select 1)) as rn' + char(10)
    select @dynamicSQL = @dynamicSQL + 'from ' + @tableName + char(10)
    if (@whereCondition is not null)
    begin
        select @dynamicSQL = @dynamicSQL + ' where ' + @whereCondition + char(10)
    end
    select @dynamicSQL = @dynamicSQL + ')' + char(10)
    
    select @dynamicSQL = @dynamicSQL + char(10)
    select @dynamicSQL = @dynamicSQL + N'select * from ' + char(10)
    select @dynamicSQL = @dynamicSQL + N'(' + char(10)
    select @dynamicSQL = @dynamicSQL + space(4) + 'select a.*' + char(10)
    select @dynamicSQL = @dynamicSQL + space(4) + 'from CTE t' + char(10)
    select @dynamicSQL = @dynamicSQL + space(4) + 'cross apply' + char(10)
    select @dynamicSQL = @dynamicSQL + space(4) + '(' + char(10)
    
    declare columnNameCursor cursor static for
    select name,system_type_id from sys.columns 
    where object_id =  object_id (@tableName) 
    order by column_id
    
    open columnNameCursor
    fetch next from  columnNameCursor into  @columnName,@type
    
    while @@fetch_status = 0
    begin
       select @dynamicSQL = @dynamicSQL + space(4) + 'select ''r''' + '+ cast(rn as nvarchar(10))'  + ',' + quotename(@columnName,'''') + ',' 

       if ( @type in (40,41,42,61))
            select @dynamicSQL = @dynamicSQL + 'convert(nvarchar(max),' + @columnName + ',121 )' + char(10)     
       else
            select @dynamicSQL = @dynamicSQL + 'convert(nvarchar(max),' + @columnName + ')' + char(10)     

       select @dynamicSQL = @dynamicSQL + space(4) + 'union all' + char(10)     
       
       fetch next from  columnNameCursor into  @columnName,@type
    end
    close columnNameCursor
    deallocate columnNameCursor
    
    select @dynamicSQL = @dynamicSQL + space(4) + 'select ''c32000''' + ',' + quotename(@columnName,'''') + ',' + '''test''' + char(10)
    select @dynamicSQL = @dynamicSQL + space(4) + ') a(id1,ColumnName,value)' + char(10)
    select @dynamicSQL = @dynamicSQL + ') m' + char(10)
    select @dynamicSQL = @dynamicSQL + 'pivot' + char(10)
    select @dynamicSQL = @dynamicSQL + '(' + char(10)
    select @dynamicSQL = @dynamicSQL + 'min(value) for id1 in (' + @PivotColumnNames + ')' + char(10)
    select @dynamicSQL = @dynamicSQL + ') P' + char(10)
    
    print @dynamicSQL
    
    exec(@dynamicSQL)
go    

If you decide to use this somewhere, please test this thoroughly, as I have just done quick unit testing.

Biggest problem with this procedure is the row size limits, so this does not scale with wider table or tables have many rows. Also, this procedure reports all the columns as NVARCHAR.

Kindly add a comment if you find this useful somewhere.