Dynamically transpose an object (Table or View) by swapping rows and columns around

The following PROC will dynamically transpose an object (Table or View) by swapping rows and columns around.

USE YourDB
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_WARNINGS OFF
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' 

+ @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
 (SELECT *
 FROM sys.objects
 WHERE name = 'TransposeObject')
 drop procedure YourSchemaTransposeObject

PRINT 'drop procedure YourSchema.TransposeObject'
GO

PRINT 'CREATE PROCEDURE YourSchema.TransposeObject'
GO

CREATE PROCEDURE YourSchema.TransposeObject
  @SchemaName NVARCHAR(256)
 ,@ObjectName NVARCHAR(256)
/*********************************************************************************
 Description : Dynamically transpose an object (Table or View) by swapping rows and columns

 Requirements: The object (Table or View which name passed as a parameter to this PROC) must have
     the following structure:
 
 ObjectName  (RowNo INT
     ,Title NVARCHAR(256)
     ,YourFieldName ATTRIBUTE
     ...
     ,YourFieldName ATTRIBUTE)
 
 Limitations: This PROC will execute a serious INSERT SELECT and UPDATE SELECT statements.

     The size of these DML commands are limited by NVARCHAR(MAX).  The PROC perfomance will decrease with

     the number of the Object fields increase.  View aliases must not have single quotes.

 Modification History:

 Date    Description
 *********************************************************************************/

AS
 BEGIN
  BEGIN TRY
   SET NOCOUNT ON;

   DECLARE  @Value    NVARCHAR(256)
     ,@Counter   INT = 1
     ,@UnpivotRecs  INT
     ,@Rec    INT = 1
     ,@strSQLivotRecs INT
     ,@strSQL   NVARCHAR(MAX);

   IF OBJECT_ID('tempdb..##tblNoRecs') IS NOT NULL DROP TABLE ##tblNoRecs;
   CREATE TABLE ##tblNoRecs(NoRecs INT);

   SET @strSQL = 'INSERT INTO ##tblNoRecs SELECT COUNT(*) FROM ' + @SchemaName + '.' + @ObjectName + ';';

   EXEC (@strSQL);
   SELECT @UnpivotRecs = NoRecs  FROM ##tblNoRecs;
   IF OBJECT_ID('tempdb..##tblPivot') IS NOT NULL DROP TABLE ##tblPivot
   SET @strSQL = 'CREATE TABLE ##tblPivot(RowNo INT, Title sql_variant'
   WHILE @Counter <= @UnpivotRecs BEGIN
    SET @strSQL = @strSQL + ',Value'  + CAST(@Counter AS VARCHAR(8)) + ' sql_variant'
    SET @Counter = @Counter + 1;
   END;
   SET @strSQL = @strSQL + ');'

   EXEC (@strSQL);
   SET @Counter = 1;
   SET @strSQL = 'INSERT INTO ##tblPivot SELECT RowNo = ORDINAL_POSITION, Title = COLUMN_NAME'
   WHILE @Counter <= @UnpivotRecs BEGIN
    SET @strSQL = @strSQL + ',Value'  + CAST(@Counter AS VARCHAR(8)) + ' =  NULL'
    SET @Counter = @Counter + 1;
   END;
   SET @strSQL = @strSQL + ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @ObjectName + ''';'
   EXEC (@strSQL);
   SET @Counter = 2;
   SET @strSQLivotRecs = (SELECT COUNT(*) FROM ##tblPivot)

   WHILE @Counter <= @strSQLivotRecs BEGIN
    SET @strSQL = 'UPDATE ##tblPivot ';
    WHILE @Rec <= @UnpivotRecs BEGIN
     IF @Rec <> 1  SET @strSQL = @strSQL + ', '
     ELSE SET @strSQL = @strSQL + ' SET ';
     
     SELECT @Value = CAST(Title AS VARCHAR(255)) FROM ##tblPivot WHERE RowNo = CAST(@Counter AS VARCHAR(8));

     SET @strSQL = @strSQL + 'Value' + CAST(@Rec AS VARCHAR(8)) + ' = (SELECT ' + @Value + ' FROM ' +  @SchemaName + '.' + @ObjectName + ' WHERE RowNo = ' + CAST(@Rec AS VARCHAR(8)) + ')';
     SET @Rec = @Rec + 1;
    END;
    SET @Rec = 1;
    SET @strSQL = @strSQL + ' WHERE RowNo = ' + CAST(@Counter AS VARCHAR(8));
    SET @Counter = @Counter + 1;

    EXEC (@strSQL);
   END; 

   SET @strSQL = '';
   SELECT @strSQL = @strSQL + NAME + ','
   FROM  tempdb.sys.columns Where object_id=OBJECT_ID('tempdb.dbo.##tblPivot');
   SET @strSQL = SUBSTRING(@strSQL, CHARINDEX(',', @strSQL) + 1, LEN(@strSQL) - CHARINDEX(',', @strSQL) - 1 );
   SET @strSQL = 'SELECT FriendlyTitle = (SELECT K2_Ext.Training.SpaceBeforeCap(CONVERT(NVARCHAR(MAX), Title))), ' + @strSQL + ' FROM ##tblPivot WHERE Title <> ''RowNo''';
  

   EXEC (@strSQL);
ExitLabel:
  END TRY
   BEGIN CATCH
    SELECT   ERROR_NUMBER() AS ErrorNumber 
      ,ERROR_SEVERITY() AS ErrorSeverity 
      ,ERROR_STATE() AS ErrorState 
      ,ERROR_PROCEDURE() AS ErrorProcedure 
      ,ERROR_LINE() AS ErrorLine 
      ,ERROR_MESSAGE() AS ErrorMessage; 
   END CATCH;
 END;
/*********************************************************************************
Test Sample:

SELECT * FROM YourSchema.YearlyFiguresUnpivot

EXEC YourDB.YourSchema.TransposeObject
 'Training'     -- @SchemaName
 ,'YearlyFiguresUnpivot'  -- @ObjectName
 

SELECT * FROMYourSchema.MonthlyFiguresUnpivot
EXEC YourDB.YourSchema.TransposeObject
 'Training'     -- @SchemaName
 ,'MonthlyFiguresUnpivot' -- @ObjectName
 
*********************************************************************************/
GO

The PROC calls the following UDF:

USE YourDB
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
      (SELECT *
    FROM sys.objects
    WHERE name = 'SpaceBeforeCap')
  DROP FUNCTION YourSchema.SpaceBeforeCap
  PRINT 'DROP FUNCTION YourSchema.SpaceBeforeCap'

GO

PRINT 'CREATE FUNCTION YourSchema.SpaceBeforeCap'
GO

CREATE FUNCTION YourSchema.SpaceBeforeCap
/*********************************************************************************
Inserts Space Before Cap

Change Log

 #    Date       Who  Change
*********************************************************************************/  (@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
 BEGIN
   DECLARE  @result NVARCHAR(MAX) = LEFT(@str, 1)
     ,@i  INT = 2;
   WHILE @i <= len(@str) BEGIN
  IF ASCII(SUBSTRING(@str, @i, 1)) BETWEEN 65 AND 90
   SET @result += ' ';
   SET @result += SUBSTRING(@str, @i, 1);
   SET @i += 1;
   END;
   RETURN @result;
 END;

/*********************************************************************************
Test Sample:

SELECT FriendlyTitle = YourDB.YourSchema.SpaceBeforeCap('ThisIsATestString')

*********************************************************************************/
GO

If you find this script useful please donate generously.