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 NM
