Dynamic Pivot in SQL Query without DECLARE
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 datacannot be posted. This query works great on database but since our Reporting Tool like Tableau etc. cannot support advance SQL like dynamic or DECLARE keywords Hence is there a way to do the same thing without DECLARE like getting Columns list (comma separated) in CTE with STRING_AGG or something Kindly 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