pivot+ oracle 11g

Version imprimable

pivot+ oracle 11g

Bonjour,

j 'ai une table appelée " tache " qui contient comme champ : (idProjet, statutProjet, nomcourt)

je veux bien avoir le nom des projet en colonne :

------------- nom1 -------- nom2 ------- nom3 ...

idProjet1 statutProjet statutProjet statutProjet

idProjet2 statutProjet statutProjet statutProjet

idProjet3 statutProjet statutProjet statutProjet

idProjet4 statutProjet statutProjet statutProjet

Pour cela, j ai ecrit la requete suivante :
Code:

1 2 3 4 5 6 7 8 9 10 11 select idProjet, statutProjet from tache PIVOT ( max(statutProjet) for nomCourt in (select listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY null) FROM (SELECT DISTINCT nomCourt AS libelle FROM tache)));
Par contre, j 'ai une erreur : ORA-00936 expression absente, sachant que :
Code:

1 2 3 4 5 select listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY null) FROM (SELECT DISTINCT nomCourt AS libelle FROM tache)
me retient bien une liste dynamique.

24/10/2011, 15h53
skuatamad

Tu ne peux pas exploiter le listagg directement. Il faut d'abord générer la liste puis l'exploiter dans une requête dynamique.
Regarde ce post :
http://www.developpez.net/forums/d11...g/#post6206926

j 'ai ecrit le code suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
var r refcursor
 declare
   courtNomList varchar2(4000);
  begin
      SELECT listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY libelle)
       INTO courtNomList FROM (SELECT DISTINCT nomcourt AS libelle FROM tache );
      open :r FOR
      ' select t.projet, t.nomcourt,t.statutProjet
        from tache t
        pivot (max (statutProjet) for (nomcourt) in ('||courtNomList||'))
        ';
   end;

par contre j a'i eu ce message d'erreur :

Code:

1
2
3
4
5
6
 
ORA-00972: l'identificateur est trop long
ORA-06512: à ligne 6
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 30 characters was specified.
*Action:   Specify at most 30 characters.

Cependant

Code:

1
2
3
4
5
SELECT  listagg( ''''||libelle||''' as "'||libelle||'"',',')  
 
within GROUP (ORDER BY NULL) FROM (SELECT DISTINCT nomCourt AS libelle 
 
  FROM tache)

fonctionne et retourne bien une liste !!

Merci d'avance

Il faut passer par une sous requête, Pivot and Unpivot :

Code:

1
2
3
4
select * 
  from (select t.projet, t.nomcourt,t.statutProjet
          from tache t)
 pivot (max (statutProjet) for (nomcourt) in ('||courtNomList||'))

j ai modifié mon code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE OR REPLACE PROCEDURE affiche() IS var r refcursor declare courtNomList varchar2(4000); begin SELECT listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY libelle) INTO courtNomList FROM (SELECT DISTINCT nomcourt AS libelle FROM tache where projet between 1 and 1000 ORDER BY nomcourt ); dbms_output.put_line(courtNomList); open :r FOR ' select * from (select t.projet, t.nomcourt,t.statuttache from tache t) pivot (max (statuttache) for (nomcourt) in ('||courtNomList||')) '; end;
Par contre j 'ai deux probleme :

1/ la procedure est compilée mais rien n'est affiché :

Citation:

Elément PROCEDURE affiche compilé
Warning: exécution terminée avec avertissement

2/ je suis obligé d'ajouter where projet between 1 and 1000 ORDER BY nomcourt
sinon j ai ce message

Citation:

ORA-00972: l'identificateur est trop long
ORA-06512: à ligne 7
00972. 00000 - "identifier is too long"
*Cause: An identifier with more than 30 characters was specified.
*Action: Specify at most 30 characters.

Merci d'avance

Syntaxiquement tu confonds du sqlplus et du pl/sql.
Si tu souhaites céer une procedure tu peux faire, selon mon précédent exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQL> create or replace procedure p (r out sys_refcursor) as
  2   joblist varchar2(4000);
  3  begin
  4    SELECT listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY libelle)
  5      INTO joblist FROM (SELECT DISTINCT job AS libelle FROM emp);
  6  
  7    open r FOR
  8         'select *
  9            from (
 10          select d.dname, e.job, e.sal
 11            from dept d
 12            join emp e on e.deptno = d.deptno
 13                 )
 14           pivot (sum(sal) for (job) in ('||joblist||'))';
 15  end;
 16  /
 
Procedure created.
 
SQL> var rc refcursor
SQL> exec p(:rc);
 
PL/SQL procedure successfully completed.
 
SQL> print rc
 
DNAME             ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING                      1300       2450       5000
RESEARCH             6000       1900       2975
SALES                            950       2850                  5600
 
SQL>

Pour voir les erreurs à la compilation depuis sqlplus faire (ça c'est pour le 1) ) :

Code:

show err

Par contre je ne sais pas s'il y a une limite en colonne pour le pivot.