Hi,

I need to create a Quick Report for a department. For this requirement, I should use pivot. However, the pivot columns must be dynamic. The columns show years and months.

To make the query dynamic, I wrote a function that returns a query text. However, when I query with Dual table. It has been returning query text, not the query result. 

The Function

CREATE OR REPLACE FUNCTION pivot_function RETURN VARCHAR2 AS
	dates       VARCHAR2(32000);
	pivot_query VARCHAR2(32000);
BEGIN

	SELECT listagg('''' || vade || ''' AS "' || vade || '"', ', ') within GROUP(ORDER BY vade)
	INTO   dates
	FROM   (

			SELECT DISTINCT to_char(t.due_date, 'YYYY') || '-' || to_char(t.due_date, 'MM') vade
			FROM   ifsapp.table_name t
			WHERE  t.odendi_bilgisi = '-'
				   AND t.is_advance_peyment_db = '+');

	pivot_query := 'SELECT *
                  FROM (
                    SELECT nvl(t.project_id, ''Projesiz'') "Proje No",
                         t.currency_rate,
                         to_char(t.due_date, ''YYYY'') || ''-'' || to_char(t.due_date, ''MM'') vade
                    FROM   ifsapp.table_name t
                    WHERE  t.odendi_bilgisi = ''-''
                         AND t.is_advance_peyment_db = ''+''
                  )
                  PIVOT (
                    SUM(currency_rate)
                    FOR vade IN (' || dates || ')
                  )';

	RETURN pivot_query;
END;

The Functions Result (Wrong - Queries itself)

The Actual Result (Must Be )

Do you have any other suggestions?