For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Hello everyone i recently successfully completed an OU object orientated programming course .What is a good program to start writing some code and creating simple apps many thanks James
That is dynamic pivoting.. Check below..
Hi,
You can use a subquery for the pivot_in_clause only in conjunction with XML. See:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#CHDFAFIE
Try the below.. Not tested...
CREATE OR REPLACE FUNCTION dynamic_fromtable RETURN SYS_REFCURSOR AS v_cursor SYS_REFCURSOR; v_sql VARCHAR2(2000); BEGIN SELECT 'SELECT * FROM(SELECT pre_sap_cod FROM pre) PIVOT(COUNT(pre_sap_cod) FOR pre_sap_cod IN('||(SELECT LISTAGG(pre_sap_cod,',') WITHIN GROUP(ORDER BY pre_sap_cod) FROM (SELECT distinct pre_sap_cod FROM pre))||'))' INTO v_sql FROM DUAL; OPEN v_cursor FOR v_sql; RETURN v_cursor; END;
CREATE OR REPLACE FUNCTION dynamic_fromtable
RETURN SYS_REFCURSOR
AS
v_cursor SYS_REFCURSOR;
v_sql VARCHAR2(2000);
BEGIN
SELECT 'SELECT *
FROM(SELECT pre_sap_cod
FROM pre)
PIVOT(COUNT(pre_sap_cod) FOR pre_sap_cod IN('||(SELECT LISTAGG(pre_sap_cod,',') WITHIN GROUP(ORDER BY pre_sap_cod) FROM (SELECT distinct pre_sap_cod FROM pre))||'))'
INTO v_sql
FROM DUAL;
OPEN v_cursor FOR v_sql;
RETURN v_cursor;
END;
Thanks,
Ann