Skip to Main Content
Forums

SQL & PL/SQL

Announcement

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!

Pivot SQL with dates

a74d0e45-ba66-4c44-a33a-910cab097cc0Feb 25 2015 — edited Feb 25 2015

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.

Comments

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

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 25 2015
Added on Feb 25 2015
#pivot
3 comments
407 views