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 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 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
PIVOT TABLE AND (MORE GENERALLY) MICROSOFT OFFICE PROBLEM
PIVOT TABLE (AND OFFICE) PROBLEM Today suddenly all my Excel financial files switched to pivot tables, and when I open Word to write this memo the top is different than before: top line is AutoSave followed by 5 symbols unfamiliar to me. I’ve used Office for decades and never had such a change before. With pivot tables I can no longer sum a series of numbers in a column, for example =sum(e1.e32) If I try this I get #FIELD! which is meaningless to me. And I can no longer put a comment on a cell with a red marker in the upper right hand corner of the cell. I don’t see how I could have unwittingly made this change in Office, both to Excel and Word – can you help? Have I somehow been hacked? I’m Robert Valtz, in France. I’d be happy to give someone on-line access to my computer, a Dell Latitude 7280.
