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:
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
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.
|
@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
-----------------------------------------------------------------------------------
