Requirement – To make a pivot table which will show output as below.
My Plsql block is 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 – The above code is producing dynamic columns enclosed in single quotation mark as shown in the picture below.
I have to remove this mark also would be helpful if I can get Grand total column as well.