I have a custom/dynamic SQL to PIVOT column values. As number of Columns are not fixed it could increase or decrease we cannot have normal PIVOT .Instead we need to identify columns dynamically and prepare a dynamic SQL as well.COLUMN_NAME and COLUMN_VALUE needs to be PIVOTED number of rows in these 02 columns may increase decrease on each day run.Posting dummy data as main data cannot be posted.This query works great on database but since our Reporting Tool like Tableau etc. cannot support advance SQL like dynamic or DECLARE keywordsHence is there a way to do the same thing without DECLARE like getting  Columns list (comma separated) in CTE with STRING_AGG or somethingKindly help me writing PIVOT which pivots columns dynamically without DECLARE 

USE [DBOOO];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [SA0067].[UNPIVOT] (
[RECORD_ID] int NULL,
[METRIC_NAME] varchar(255) NULL,
[NAME] varchar(255) NULL,
[COLUMN_Name] nvarchar(4000) NULL,
[COLUMN_VALUE] nvarchar(255) NULL)
)
;
GO
—-INSERT statement
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (1,’ABC’,’AJ’,’ID’,1);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (2,’ABC’,’AK’,’ID’,6);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (3,’XYZ’,’RJ’,’ID’,4);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (4,’XYZ’,’JK’,’ID’,5);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (5,’XYZ’,’JJJ’,’ID’,11);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (6,’REC’,’MJ’,’ID’,42;
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (7,’REC’,’SG’,’ID’,33);

— DYNAMIC SQL thats needs to be re-written
DECLARE @COLUMNNAMES NVARCHAR(MAX)=”
DECLARE @SQL NVARCHAR(MAX)=”
;WITH COL_NM
AS
(SELECT DISTINCT ‘[‘+COLUMN_NAME+’],’ AS CN FROM DBOOO.[SA0067].[UNPIVOT])
SELECT @COLUMNNAMES +=CN FROM COL_NM
SET @COLUMNNAMES =LEFT(@COLUMNNAMES,LEN(@COLUMNNAMES)-1)
SET @SQL =’SELECT * FROM (
SELECT RECORD_ID,METRIC_NAME,NAME,COLUMN_Name,COLUMN_Value

FROm DBOOO.[SA0067].[UNPIVOT]
) t
PIVOT
( MAX(COLUMN_Value)
FOR COLUMN_Name IN (‘+@COLUMNNAMES+’)
) AS PT’
EXECUTE sp_executesql @SQL

​I have a custom/dynamic SQL to PIVOT column values. As number of Columns are not fixed it could increase or decrease we cannot have normal PIVOT .Instead we need to identify columns dynamically and prepare a dynamic SQL as well.COLUMN_NAME and COLUMN_VALUE needs to be PIVOTED number of rows in these 02 columns may increase decrease on each day run.Posting dummy data as main data cannot be posted.This query works great on database but since our Reporting Tool like Tableau etc. cannot support advance SQL like dynamic or DECLARE keywordsHence is there a way to do the same thing without DECLARE like getting  Columns list (comma separated) in CTE with STRING_AGG or somethingKindly help me writing PIVOT which pivots columns dynamically without DECLARE   USE [DBOOO];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [SA0067].[UNPIVOT] (
[RECORD_ID] int NULL,
[METRIC_NAME] varchar(255) NULL,
[NAME] varchar(255) NULL,
[COLUMN_Name] nvarchar(4000) NULL,
[COLUMN_VALUE] nvarchar(255) NULL)
)
;
GO
—-INSERT statement
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (1,’ABC’,’AJ’,’ID’,1);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (2,’ABC’,’AK’,’ID’,6);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (3,’XYZ’,’RJ’,’ID’,4);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (4,’XYZ’,’JK’,’ID’,5);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (5,’XYZ’,’JJJ’,’ID’,11);
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (6,’REC’,’MJ’,’ID’,42;
INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (7,’REC’,’SG’,’ID’,33);

— DYNAMIC SQL thats needs to be re-written
DECLARE @COLUMNNAMES NVARCHAR(MAX)=”
DECLARE @SQL NVARCHAR(MAX)=”
;WITH COL_NM
AS
(SELECT DISTINCT ‘[‘+COLUMN_NAME+’],’ AS CN FROM DBOOO.[SA0067].[UNPIVOT])
SELECT @COLUMNNAMES +=CN FROM COL_NM
SET @COLUMNNAMES =LEFT(@COLUMNNAMES,LEN(@COLUMNNAMES)-1)
SET @SQL =’SELECT * FROM (
SELECT RECORD_ID,METRIC_NAME,NAME,COLUMN_Name,COLUMN_Value

FROm DBOOO.[SA0067].[UNPIVOT]
) t
PIVOT
( MAX(COLUMN_Value)
FOR COLUMN_Name IN (‘+@COLUMNNAMES+’)
) AS PT’
EXECUTE sp_executesql @SQL    Read More

​