Friday, February 3, 2017

Dynamically Pivot an UnPivoted table in SQL Server

Sometimes we have to Pivot/De-normalize source/output data which arrives as UnPivot/Normalize.
For that, I have created a dynamic procedure, which will create a new table with Pivoted data. And it’s very simple to call and get data here or from a table, both ways are open.
Let me show by an example …


See Also: SQL script to dynamically UnPivot any Pivot table of SQL Server with example

              Send mail as html format from SQL Server DB with attached file and/or embedded picture/video


Here is our input data (UnPivot)
UnPivot Table
GEO_ID
PERIOD_ID
UNIT
VALUE
SRC
G0001
P01012017
200
1235.2
IN
G0002
P01022017
300
1524.96
OUT
G0003
P01032017
150
123
OUT
G0004
P01042017
100
458
IN
G0002
P01022017
100
456
IN

And below would be the final Pivoted data for the above … if we consider the SRC column to Pivot data and for sure GEO_ID and PERIOD_ID are the dimensions and UNIT and VALUE are the measures
Pivot Table
GEO_ID
PERIOD_ID
IN_UNIT
OUT_UNIT
IN_VALUE
OUT_VALUE
G0001
P01012017
200
NULL
1235.2
NULL
G0002
P01022017
100
300
456
1524.96
G0003
P01032017
NULL
150
NULL
123
G0004
P01042017
100
NULL
458
NULL

To do this you have to follow the below steps...
     1.     Download the Procedure PivotTable.sql … below I provide the code also, in case of download failed. Run the script to create dbo.PivotTable.
         2.     Execute the procedure, make sure supply enough value/parameter to Pivot
        3.     Query on new Pivot table … DEFAULT is, new table name would be the same as input table name but a suffix like “_Pvt”.

   NOTE: i have used a procedure to Split string named dbo.splitString(sourceString varchar, delimeter varchar). you can find it here SQL Script to split string using SQL Server

So execute the procedure like below one...

EXEC ABC_GLOBAL.dbo.PivotTable
  @dbname = 'ABC_DEFAULT'
            , @tableNameWithSchema = 'dbo.Test_UnPivot'
            , @searchMeasureName = 0
            , @measureNames = 'UNIT,VALUE'
            , @aggregateOperationName = 'AVG,AVG'
            , @dimColumnNames = 'GEO_ID,PERIOD_ID'
            , @pvtColumnName = 'SRC'
            , @pivotTableNameWithSchema = 'dbo.Test_UnPivot_Pivot_New'
            , @orderPivotDataBy = ‘PERIOD_ID

Let’s check the parameter descriptions before use J

Parameter
Type
Description
@dbName
VARCHAR(100)
Database Name, where the Un-Pivot table
@tableNameWithSchema
VARCHAR(100)
Table name with schema name. ie dbo.test
@searchMeasureName
BIT
Default is 0;
This ensure that measure columns should found by the search string, provided in the next parameter, @ measureNames.
@measureNames
VARCHAR(MAX)
Provide the measure column names separated by comma (,). If @searchMeasureName=1, provide the search string, also separated by comma (,). Ie: ‘[UNIT],[VALUE]’ OR ‘un,val’
@aggregateOperationName
VARCHAR(MAX)
Can't be leave as NULL.
Provide aggregation function name to apply on respective measure names of @measureNames. Separated by comma (,) if more than one. Must be sync with @measureNames.
i.e if @measureNames=’A,B’ then @aggregateOperationName=’AVG,SUM’
@dimColumnNames
VARCHAR(MAX)
Provide the unchanged column names.
@pvtColumnName
VARCHAR(100)
Provide column name, which one would be used to Pivot.
@pivotTableNameWithSchema
VARCHAR(MAX)
NULL is default – then new table name would be “Test_Table_ptv”.
Or provide a Name for the new table.
@orderPivotDataBy
VARCHAR(MAX)
List the column name(s) to order the pivoted data.


Below is the script for “ABC_GLOBAL.dbo.PivotTable
-----------------------------------------------------------------------------------
CREATE PROCEDURE [ABC_GLOBAL][dbo].[PivotTable]
      @dbName VARCHAR(100) = NULL
                , @tableNameWithSchema VARCHAR(100) = NULL
                , @searchMeasureName BIT = 0
                , @measureNames VARCHAR(MAX) = NULL
                , @aggregateOperationName VARCHAR(MAX) = NULL
                , @dimColumnNames VARCHAR(MAX) = NULL
                , @pvtColumnName VARCHAR(100) = NULL
                , @pivotTableNameWithSchema VARCHAR(MAX) = NULL
                , @orderPivotDataBy VARCHAR(MAX) = NULL
AS
BEGIN
                --EXEC GILEAD_GLOBAL.dbo.PivotTable @dbname = 'GILEAD_DEFAULT'
                --             , @tableNameWithSchema = 'dbo.Test_UnPivot'
                --             , @searchMeasureName = 0
                --             , @measureNames = 'UNIT,VALUE'
                --             , @aggregateOperationName = N'AVG,SUM'
                --             , @dimColumnNames = 'GEO_ID,PERIOD_ID'
                --             , @pvtColumnName = 'SRC'
                --             , @pivotTableNameWithSchema = NULL
                --             , @orderPivotDataBy = NULL

                SET NOCOUNT ON
                SET @measureNames = ltrim(rtrim(@measureNames))
                SET @pvtColumnName = ltrim(rtrim(@pvtColumnName))
                SET @dbName = CASE
                                                WHEN @dbName IS NULL
                                                                OR ltrim(rtrim(@dbName)) = ''
                                                                THEN QUOTENAME(DB_NAME())
                                                ELSE ltrim(rtrim(@dbName))
                                                END
                SET @tableNameWithSchema = ltrim(rtrim(@tableNameWithSchema))

                DECLARE @fullTableName VARCHAR(200) = @dbName + '.' + @tableNameWithSchema
                DECLARE @newTableName VARCHAR(200) = CASE
                                                WHEN @pivotTableNameWithSchema IS NULL
                                                                OR @pivotTableNameWithSchema = ''
                                                                THEN CASE
                                                                                                WHEN right(@fullTableName, 1) IN (
                                                                                                                                ']'
                                                                                                                                , '"'
                                                                                                                                )
                                                                                                                THEN substring(@fullTableName, 1, LEN(@fullTableName) - 1) + '_Pvt]'
                                                                                                ELSE @fullTableName + '_Pvt'
                                                                                                END
                                                ELSE @dbName + '.' + @pivotTableNameWithSchema
                                                END
                --Tables
                DECLARE @columnNames TABLE (ColumnName VARCHAR(100))
                DECLARE @pvtValues TABLE (
                                ID INT
                                , pvtValue NVARCHAR(MAX)
                                );

                INSERT INTO @columnNames
                EXEC (
                                                'SELECT QUOTENAME(NAME)
                FROM ' + @dbName + '.sys.columns
                WHERE object_id = OBJECT_ID(''' + @fullTableName + ''')'
                                                )

                INSERT INTO @pvtValues
                EXEC ('select ROW_NUMBER() OVER(ORDER BY ' + @pvtColumnName + ' ) as ID,' + @pvtColumnName + ' from ' + @fullTableName + ' GROUP BY ' + @pvtColumnName
                                                );

                --final query to generate SQL
                DECLARE @pvtString NVARCHAR(MAX) = '';

                WITH 
                ClauseStrings
                AS (
                                SELECT ID
                                                , MeasureName
                                                , DimColName
                                                , selectCol
                                                , ' pivot(' + a.aggrOpName + '(' + MeasureName + ') FOR ' + DimColName + 'IN( ' pvtString
                                FROM (
                                                SELECT ROW_NUMBER() OVER (
                                                                                ORDER BY cs.OrdinalPosition
                                                                                ) ID
                                                                , cn.ColumnName MeasureName
                                                                , aon.Value aggrOpName
                                                                , QUOTENAME(@pvtColumnName + cn.ColumnName) DimColName
                                                                , ',' + @pvtColumnName + '+''' + cn.ColumnName + ''' AS ' + QUOTENAME(@pvtColumnName + cn.ColumnName) selectCol
                                                FROM dbo.SplitString(@measureNames, ',') cs
                                                INNER JOIN dbo.SplitString(@aggregateOperationName, ',') aon
                                                                ON aon.OrdinalPosition = cs.OrdinalPosition
                                                INNER JOIN @columnNames cn
                                                                ON cn.ColumnName = CASE
                                                                                                WHEN LEFT(cs.Value, 1) = '['
                                                                                                                AND RIGHT(cs.Value, 1) = ']'
                                                                                                                THEN cs.Value
                                                                                                ELSE QUOTENAME(cs.Value)
                                                                                                END
                                                                                OR cn.ColumnName LIKE CASE
                                                                                                WHEN @searchMeasureName = 1
                                                                                                                THEN '%' + cs.Value + '%'
                                                                                                ELSE '-'
                                                                                                END
                                                ) a
                                )
                                , 
                columnList
                AS (
                                SELECT ROW_NUMBER() OVER (
                                                                PARTITION BY c.DimColName ORDER BY p.pvtValue
                                                                ) ID
                                                , QUOTENAME(p.pvtValue + c.MeasureName) pvtIntCol
                                                , c.DimColName
                                                , ' avg(' + QUOTENAME(p.pvtValue + c.MeasureName) + ') as ' + QUOTENAME(p.pvtValue + '_' + replace(replace(c.MeasureName, '[', ''), ']', '')) finalCol
                                FROM @pvtValues p
                                CROSS JOIN ClauseStrings c
                                )
                                , 
                 finalTable
                AS (
                                SELECT 0 AS ID
                                                , b.DimColName
                                                , b.MeasureName
                                                , b.selectCol
                                                , b.pvtString + left(a.pvtInner, LEN(a.pvtInner) - 1) + ')) AS pvt' + CAST(b.ID AS VARCHAR) AS pvtCluse
                                                , a.finalCol
                                FROM (
                                                SELECT (
                                                                                SELECT b.pvtIntCol + ', '
                                                                                FROM columnList b
                                                                                WHERE a.DimColName = b.DimColName
                                                                                FOR XML path('')
                                                                                ) pvtInner
                                                                , DimColName
                                                                , (
                                                                                SELECT ', ' + b.finalCol
                                                                                FROM columnList b
                                                                                WHERE a.DimColName = b.DimColName
                                                                                FOR XML path('')
                                                                                ) finalCol
                                                FROM columnList a
                                                GROUP BY DimColName
                                                ) a
                                INNER JOIN ClauseStrings b
                                                ON a.DimColName = b.DimColName
                                )
                SELECT @pvtString = 'SELECT ' + @dimColumnNames + (
                                                SELECT b.finalCol + ' '
                                                FROM finalTable b
                                                WHERE a.ID = b.ID
                                                FOR XML path('')
                                                ) + ' INTO ' + @newTableName + ' FROM (SELECT ' + @dimColumnNames + (
                                                SELECT ' , ' + b.MeasureName
                                                FROM finalTable b
                                                WHERE a.ID = b.ID
                                                FOR XML path('')
                                                ) + (
                                                SELECT b.selectCol + ' '
                                                FROM finalTable b
                                                WHERE a.ID = b.ID
                                                FOR XML path('')
                                                ) + ' from ' + @fullTableName + ') upvtbl ' + CHAR(10) + (
                                                SELECT b.pvtCluse + ' '
                                                FROM finalTable b
                                                WHERE a.ID = b.ID
                                                FOR XML path('')
                                                ) + ' group by ' + @dimColumnNames
                FROM finalTable a

                --delete existing new_table = @newTableName
                EXEC (
                                                'IF  EXISTS (SELECT * FROM ' + @dbname + '.sys.objects WHERE object_id = OBJECT_ID(N''' + @newTableName + ''') AND type in (N''U'')) DROP TABLE ' +
                                                @newTableName
                                                )

                --ceate new pivoted new_table = @newTableName
                IF ISNULL(@orderPivotDataBy, '') <> ''
                BEGIN
                                SET @pvtString = @pvtString + ' Order by ' + @orderPivotDataBy
                END

                EXEC (@pvtString)
    EXEC ('SELECT * FROM ' + @newTableName)
                --PRINT cast(@pvtString AS TEXT)
                PRINT 'SELECT * FROM ' + @newTableName

END
-----------------------------------------------------------------------------------