Requirement: to create a pivot table which will show output like this:
My PL/SQL block looks like this:
DECLARE
v_sql VARCHAR2(4000);
v_months VARCHAR2(1000);
BEGIN
-- Generate the list of months dynamically for the pivot clause
FOR i IN 0..11 LOOP
v_months := v_months ||''''|| TO_CHAR(ADD_MONTHS(SYSDATE, i), 'MonYYYY') || ''', ';
END LOOP;
-- Remove the trailing comma and space
v_months := RTRIM(v_months, ', ');
-- Construct the dynamic SQL for creating the table
v_sql := 'CREATE TABLE dynamic_pivot_table_test AS ' ||
'SELECT * FROM (' ||
'SELECT part, TO_CHAR(begin_date, ''MonYYYY'') AS begin_date_YM, SUM(new_buy_units) AS SUM_UNITS ' ||
'FROM one_mpbr ' ||
'WHERE begin_date >= TRUNC(SYSDATE, ''MM'') ' ||
'AND begin_date < ADD_MONTHS(TRUNC(SYSDATE, ''MM''), 12) ' ||
'GROUP BY part, TO_CHAR(begin_date, ''MonYYYY'')) ' ||
'PIVOT (SUM(SUM_UNITS) FOR begin_date_YM IN (' || v_months || '))';
-- Print the dynamic SQL query
DBMS_OUTPUT.PUT_LINE(v_sql);
-- Execute the dynamic SQL query to create the table
EXECUTE IMMEDIATE v_sql;
END;
/
Problem: this code is producing dynamic columns enclosed in single quotation mark as shown in the screenshot here:
I have to remove this mark also would be helpful if I can get Grand total column as well.
You need to alias your columns, e.g. in the PIVOT part, it needs to be:
begin_date_ym IN('May2024' AS "May2024",
'Jun2024' AS "Jun2024",
...)
which you can do by amending your procedure to include the alias:
v_months := v_months ||''''|| TO_CHAR(ADD_MONTHS(SYSDATE, i), 'MonYYYY') ||
''' AS "' || TO_CHAR(ADD_MONTHS(SYSDATE, i), 'MonYYYY') || '", ';
See this db<>fiddle for an example