Forum Discussion
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 ...
Can you write the script into a stored procedure, and then call the sp from Reporting Tool?
- SP - AS reporting team do not have access.
- I tried fetching all values comma separated (STRING_AGG) exactly in same format as that of my column name variables but was unable to pass in dynamic select
- kindly helpmif instead of declare PLSQL block same can be achieved by normal SQL sub queries/ cte/ etc.
Is this fits you?
CREATE TABLE #col (c nvarchar(10)) GO INSERT INTO #col VALUES('A'),('B'),('C'),('D'),('E') GO DECLARE @SQL NVARCHAR(MAX) WITH NM AS ( SELECT STRING_AGG(c, ',') AS N FROM #col ) SELECT @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 ('+ N + ') ) AS PT' FROM NM SELECT @SQL DROP TABLE #colrodgerkong it still uses DECLARE which is something TABLEAU will not support
if we could code something like
WITH NM AS ( SELECT STRING_AGG(c, ',') AS N FROM #col ) SELECT @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 (SELECT N from NM) ) AS PT' FROM NMThe list of columns defined in the IN clause cannot be expressions or variables, so the only way to make it dynamic is dynamic SQL. Since report tool cannot use dynamic SQL, your problem have no solution.
