-----------------------------------------------------------------------------------
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
-----------------------------------------------------------------------------------
