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?
