Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

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.


This post first appeared on Nothing Is Bug Free, please read the originial post: here

Share the post

Dynamically Pivot an UnPivoted table in SQL Server

×

Subscribe to Nothing Is Bug Free

Get updates delivered right to your inbox!

Thank you for your subscription

×