Thursday, July 13, 2017

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

No comments:

Post a Comment