Pivot SQL with dates

a74d0e45-ba66-4c44-a33a-910cab097cc0

Hello,

I have the following query:

select *

    from

    (select pre_sap_cod

     from pre

    )

    pivot

    (

            count(pre_sap_cod)

            for pre_sap_cod in ('1' , '3')

    );

This is working fine,I get what I want:

   '1'     |      '3'

8708   |   281259

But there are more values that 1  and  3, and if I try to do this;

select *

    from

    (select pre_sap_cod

     from pre

    )

    pivot

    (

            count(pre_sap_cod)

            for pre_sap_cod in (select distinct pre_sap_cod from  pre)

    );

I get the error message:  ORA-00936: missing expression

What I can do?.   I am using Pivot because I want the information in columns.

Thanks in advance.

AnnEdmund

That is dynamic pivoting.. Check below..

GregV

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

AnnEdmund

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;

Thanks,

Ann