Forum Discussion

VBA_ENTHU1988's avatar
VBA_ENTHU1988
Copper Contributor
Oct 11, 2024

Dynamic Pivot in SQL Query without DECLARE

  1. I have a custom/dynamic SQL to PIVOT column values. 
  2. As number of Columns are not fixed it could increase or decrease we cannot have normal PIVOT .
  3. Instead we need to identify columns dynamically and prepare a dynamic SQL as well.
  4. COLUMN_NAME and COLUMN_VALUE needs to be PIVOTED number of rows in these 02 columns may increase decrease on each day run.
  5. Posting dummy data as main data cannot be posted.
  6. This query works great on database 
  7. but since our Reporting Tool like Tableau etc. cannot support advance SQL like dynamic or DECLARE keywords
  8. 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
  9. 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

 

 

Data Warehouse
Pivot
PIVOT TABLE PROBLEM
sql server

7 Replies

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Oct 12, 2024
    Can you write the script into a stored procedure, and then call the sp from Reporting Tool?
    • VBA_ENTHU1988's avatar
      VBA_ENTHU1988
      Copper Contributor
      Oct 13, 2024

      rodgerkong 

      • 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.

       

      • rodgerkong's avatar
        rodgerkong
        Iron Contributor
        Oct 13, 2024

        VBA_ENTHU1988 

        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 #col

Resources