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'
, @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. |