Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab (month,value) AS
SELECT 1,100 FROM dual UNION ALL
SELECT 2,200 FROM dual UNION ALL
SELECT 3,300 FROM dual UNION ALL
SELECT 4,400 FROM dual UNION ALL
SELECT 5,500 FROM dual UNION ALL
SELECT 6,600 FROM dual
6 rows affected

CREATE OR REPLACE FUNCTION Get_Month_Values RETURN VARCHAR2 IS
  v_recordset VARCHAR2(4000);
  v_sql       VARCHAR2(32767);
  v_cols      VARCHAR2(32767);
BEGIN
  SELECT LISTAGG( ''''||month||''' AS "MONTH_'||TO_CHAR( TO_DATE(month,'mm') ,'MON')||'"' , ',' )
                 WITHIN GROUP ( ORDER BY month )
    INTO v_cols
    FROM tab;

  v_sql :=
  'SELECT *
     FROM tab t
    PIVOT
    (
     MAX(value) FOR month IN ( '|| v_cols ||' )
    )';

--OPEN v_recordset FOR v_sql;
  RETURN v_sql; -- v_recordset;
  
END;
/
DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := Get_Month_Values;
  DBMS_OUTPUT.PUT_LINE(v_sql);
END;
/
1 rows affected

dbms_output:
SELECT *
     FROM tab t
    PIVOT
    (
     MAX(value) FOR month IN ( '1' AS "MONTH_JAN",'2' AS "MONTH_FEB",'3' AS "MONTH_MAR",'4' AS "MONTH_APR",'5' AS "MONTH_MAY",'6' AS "MONTH_JUN" )
    )

--# Sample Demo for the query generated by PIVOT Clause case
SELECT *
  FROM tab t
 PIVOT
 (
  MAX(value) FOR month IN ( '1' AS "MONTH_JAN",'2' AS "MONTH_FEB",'3' AS "MONTH_MAR",'4' AS "MONTH_APR",'5' AS "MONTH_MAY",'6' AS "MONTH_JUN" )
 )


MONTH_JAN
MONTH_FEB
MONTH_MAR
MONTH_APR
MONTH_MAY
MONTH_JUN


100
200
300
400
500
600


CREATE OR REPLACE FUNCTION Get_Month_Values RETURN VARCHAR2 IS
  v_recordset VARCHAR2(4000);
  v_sql       VARCHAR2(32767);
  v_cols      VARCHAR2(32767);
BEGIN
  SELECT LISTAGG('MAX( CASE WHEN month =  '''||month||''' THEN '||value||' END ) AS "MONTH_'||TO_CHAR( TO_DATE(month,'mm') ,'MON')||'"' , ',' )
                 WITHIN GROUP ( ORDER BY month )                 
    INTO v_cols
    FROM tab;

  v_sql := 'SELECT '|| v_cols ||' FROM tab';

--OPEN v_recordset FOR v_sql;
  DBMS_OUTPUT.PUT_LINE(v_sql);
  RETURN v_sql; -- v_recordset;
END;
/
DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := Get_Month_Values;
  DBMS_OUTPUT.PUT_LINE(v_sql);
END;
/
1 rows affected

dbms_output:
SELECT MAX( CASE WHEN month =  '1' THEN 100 END ) AS "MONTH_JAN",MAX( CASE WHEN month =  '2' THEN 200 END ) AS "MONTH_FEB",MAX( CASE WHEN month =  '3' THEN 300 END ) AS "MONTH_MAR",MAX( CASE WHEN month =  '4' THEN 400 END ) AS "MONTH_APR",MAX( CASE WHEN month =  '5' THEN 500 END ) AS "MONTH_MAY",MAX( CASE WHEN month =  '6' THEN 600 END ) AS "MONTH_JUN" FROM tab
SELECT MAX( CASE WHEN month =  '1' THEN 100 END ) AS "MONTH_JAN",MAX( CASE WHEN month =  '2' THEN 200 END ) AS "MONTH_FEB",MAX( CASE WHEN month =  '3' THEN 300 END ) AS "MONTH_MAR",MAX( CASE WHEN month =  '4' THEN 400 END ) AS "MONTH_APR",MAX( CASE WHEN month =  '5' THEN 500 END ) AS "MONTH_MAY",MAX( CASE WHEN month =  '6' THEN 600 END ) AS "MONTH_JUN" FROM tab

--# Sample Demo for the query generated by Conditional Aggregation case
SELECT MAX( CASE WHEN month =  '1' THEN 100 END ) AS "MONTH_JAN",
       MAX( CASE WHEN month =  '2' THEN 200 END ) AS "MONTH_FEB",
       MAX( CASE WHEN month =  '3' THEN 300 END ) AS "MONTH_MAR",
       MAX( CASE WHEN month =  '4' THEN 400 END ) AS "MONTH_APR",
       MAX( CASE WHEN month =  '5' THEN 500 END ) AS "MONTH_MAY",
       MAX( CASE WHEN month =  '6' THEN 600 END ) AS "MONTH_JUN" 
  FROM tab


MONTH_JAN
MONTH_FEB
MONTH_MAR
MONTH_APR
MONTH_MAY
MONTH_JUN


100
200
300
400
500
600