un pivot 'dynamique' pour oracle 10g

Bonjour voila j'ai une table BILAN qui contient différente matiere avec des eleves j'aimerai faire un pivot pour avoir un resultat qui ressemble à çà :

Code:

---

1 2 3

NOM MATIERE1 MATIERE2 ETC.. ELEVE1 12 13 ELEVE2 14 11

---

Voici ma procédure stockée pour le moment :

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

CREATE OR REPLACE PROCEDURE pivotBilan AS v_matiere VARCHAR2(25); v_req_deb VARCHAR2(50); v_req_fin VARCHAR(50); v_req VARCHAR2(300); v_req_final VARCHAR(500); v_long NUMBER; CURSOR monCurseur IS SELECT DISTINCT matiere FROM BILAN ORDER BY matiere BEGIN OPEN monCurseur; v_long:=0; v_req_deb := 'SELECT nom,'; v_req_fin := 'FROM bilan GROUP BY nom'; FETCH monCurseur INTO v_matiere; WHILE(monCurseur%FOUND) LOOP v_req:= CONCAT(v_req, "max(decode(matiere,'"v_matiere"',moyenne)),"); FETCH monCurseur INTO v_matiere; END LOOP; CLOSE monCurseur; v_long:=LENGTH(v_req); v_req:=SUBSTR(v_req,v_long-1,0); v_req_final := v_req_deb || v_req || v_req_fin; EXECUTE IMMEDIATE v_req_final; END; /

---

çà me retourne l'erreur suivante :

Code:

---

1 2 3 4 5 6

ERREUR à la ligne 13 : PLS-00103: Symbole "=" rencontré à la place d'un des symboles suivants :   constant exception table LONG_ double ref char time timestamp interval date binary national character nchar

---

la ligne 13 est :

Code:

---

v_req_fin := 'FROM bilan GROUP BY nom';

---

Je vous remercie par avance pour votre aide !

Bonjour,
Il y a plusieurs problèmes dans ton code :
- il manque le ";" à la fin de la déclaration de "monCurseur",
- à la ligne 17, il faut utiliser des apostrophes et non des doubles quotes ; personnellement je préfère utiliser le double pipe "||" plutôt que la fonction CONCAT :

Code:

---

v_req:= v_req || 'max(decode(matiere,''' || v_matiere || ''',moyenne)),';

---

Xavier

Pourquoi faire un PLSQL ici ???:koi:


Il y a une reponse en SQL deja ...

Quand tu peux le faire en SQL reste en SQL !!!

@fatsora : Avec le SQL cela m'oblige à connaitre les matières à l'avance il me semble, je ne connais pas à l'avance les matieres présentent dans ma table BILAN.

@xdescamp : Merci beaucoup, ma procédure se crée bien grâce à tes corrections.

Par contre lorsque je la test :

Code:

---

1 2 3 4 5

BEGIN pivotBilan(); END; /

---

j'obtiens l'erreur suivante :

Code:

---

ORA-00936: expression absente

---

Sais-tu d'ou celà peut venir ?
Voici ma procédure mise à jour :

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

CREATE OR REPLACE PROCEDURE pivotBilan AS v_matiere VARCHAR2(25); v_req_deb VARCHAR2(50); v_req_fin VARCHAR(50); v_req VARCHAR2(300); v_req_final VARCHAR(500); v_long NUMBER; CURSOR monCurseur IS SELECT DISTINCT matiere FROM BILAN ORDER BY matiere; BEGIN OPEN monCurseur; v_long:=0; v_req_deb := 'SELECT nom,'; v_req_fin := 'FROM bilan GROUP BY nom'; FETCH monCurseur INTO v_matiere; WHILE(monCurseur%FOUND) LOOP v_req:= v_req || 'max(decode(matiere,''' || v_matiere || ''',moyenne)),'; FETCH monCurseur INTO v_matiere; END LOOP; CLOSE monCurseur; v_long:=LENGTH(v_req); v_req:=SUBSTR(v_req,v_long-1,0); v_req_final := v_req_deb || v_req || v_req_fin; EXECUTE IMMEDIATE v_req_final; END; /

---

Merci beaucoup !

Ce que tu essaies de faire est complique, est-ce vraiment utile ?
Tu as un nombre de colonne dynamique, ce qui n'est franchement pas commode pour recuperer/manipuler les donnees. Qu'en fais-tu ?
Le plus simple serait d'eviter PL/SQL, mais d'avoir un petit SQL qui creer un autre script avec toutes les colonnes comme il faut, juste pour un rapport.

Quant a ton erreur "ORA-00936: expression absente" elle provient de l'"execute immediate", un cauchemar a debugger, et en plus sans clause INTO. Et la on rejoint ce que je disais auparavant, comment va tu faire pour savoir quoi metter dans la clause INTO ? Puisque par definition, tu ne connais pas les colonnes que tu vas recuperer.

Nicolas.

Enfaite, j'ai une table BILAN vide à la base avec comme champs

NOM, MATIERE, MOYENNE

Celle-ci je la garni avec une procédure

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 34 35 36 37 38 39 40 41 42

CREATE OR REPLACE PROCEDURE maProcedure(numeroClasse in NUMBER) AS v_nomEleve VARCHAR2(25); v_prenomEleve VARCHAR2(25); v_libelleMatiere VARCHAR2(25); v_MatCourante VARCHAR2(25); v_NomCourant VARCHAR2(25); v_total NUMBER; v_moyenne NUMBER(4,2); v_nbr NUMBER; v_note NUMBER; CURSOR monCurseur IS SELECT e.nomEleve, n.note, m.libelleMatiere FROM eleves e, notes n, matieres m, evaluations ev, classes c WHERE e.numeleve=ev.numeleve AND n.numnote=ev.numnote AND m.nummatiere=ev.nummatiere AND c.numClasse=ev.numClasse AND c.numClasse=numeroClasse ORDER BY e.nomEleve, m.libelleMatiere ASC; BEGIN OPEN monCurseur; v_nbr:=0; v_total:=0; v_note:=0; FETCH monCurseur INTO v_nomEleve, v_note, v_libelleMatiere; WHILE (monCurseur%FOUND) LOOP v_MatCourante:=v_libelleMatiere; v_NomCourant:=v_nomEleve; WHILE((monCurseur%FOUND)AND(v_MatCourante=v_libelleMatiere)AND(v_NomCourant=v_nomEleve)) LOOP v_nbr:=v_nbr+1; v_total:=v_total+v_note; fetch monCurseur INTO v_nomEleve, v_note, v_libelleMatiere; END LOOP; v_moyenne:=v_total/v_nbr; INSERT INTO BILAN (nom,matiere,moyenne) VALUES (v_NomCourant,v_MatCourante,v_moyenne); END LOOP; CLOSE monCurseur; END; /

---

Donc après cette procédure, ma table bilan contient les moyennes de tous les élèves d'UNE classe passé en paramètre pour chaque matières enseignés à cette classe. (Toutes les classes non pas les mêmes matières enseignés)

Et ce que j'aimerai faire, c'est d'afficher cette table BILAN en pivot, j'ai donc pensé le faire en PL/SQL mais si il y à une autre je suis à l'écoute.

Je n'ai pas bien compris ta proposition "petit SQL qui creer un autre script avec toutes les colonnes" pourrais-tu m'en dire plus ?


Merci à vous !

Si ta table est vide, la requête de ton EXECUTE IMMEDIATE vaut:

Code:

---

SELECT nom, FROM bilan GROUP BY nom

---

Ca plante à cause de la virgule.

Quand on crée une requête dynamiquement, il vaut mieux ajouter les virgules au début de la chaîne ajoutée.

Code:

---

1 2 3 4 5 6 7 8

v_req_deb := 'SELECT nom '; -- pas de virgule ici v_req_fin := 'FROM bilan GROUP BY nom'; FETCH monCurseur INTO v_matiere; WHILE(monCurseur%FOUND) LOOP v_req:= v_req || ' ,max(decode(matiere,''' || v_matiere || ''',moyenne)) '; -- une virgule au début, pas à la fin FETCH monCurseur INTO v_matiere; END LOOP;

---

De plus, ça t'évite de bidouiller la requête avec ça, qui devient inutile:

Code:

---

1 2	v_long:=LENGTH(v_req); v_req:=SUBSTR(v_req,v_long-1,0);

---

Citation:

---

Quand on crée une requête dynamiquement, il vaut mieux ajouter les virgules au début de la chaîne ajoutée.

---

C'est vrai, mais dans le cas precis, ca ne solutionne pas le fait que l'on ne connais pas le nombre de colonnes a l'avance pour pouvoir les recuperer.

Nicolas.

Bien entendu. Je me contentais de corriger son erreur.
S'il tient à le faire en PL/SQL, il peut récupérer le nom des colonnes et les moyennes (éventuellement NULL) dans un tableau, et traiter après.

Citation:

---

Envoyé par GoLDoZ

Si ta table est vide, la requête de ton EXECUTE IMMEDIATE vaut:

Code:

---

SELECT nom, FROM bilan GROUP BY nom

---

Ca plante à cause de la virgule.

Quand on crée une requête dynamiquement, il vaut mieux ajouter les virgules au début de la chaîne ajoutée.

Code:

---

1 2 3 4 5 6 7 8

v_req_deb := 'SELECT nom '; -- pas de virgule ici v_req_fin := 'FROM bilan GROUP BY nom'; FETCH monCurseur INTO v_matiere; WHILE(monCurseur%FOUND) LOOP v_req:= v_req || ' ,max(decode(matiere,''' || v_matiere || ''',moyenne)) '; -- une virgule au début, pas à la fin FETCH monCurseur INTO v_matiere; END LOOP;

---

De plus, ça t'évite de bidouiller la requête avec ça, qui devient inutile:

Code:

---

1 2	v_long:=LENGTH(v_req); v_req:=SUBSTR(v_req,v_long-1,0);

---

---

Merci de ton aide et de tes conseils, je peux pas testé çà maintenant je le fait en rentrant chez moi !

Citation:

---

Envoyé par NGasparotto

C'est vrai, mais dans le cas precis, ca ne solutionne pas le fait que l'on ne connais pas le nombre de colonnes a l'avance pour pouvoir les recuperer.
Nicolas.

---

Je comprends pas bien pourquoi tu dis çà, je m'explique,
ma table BILAN (NOM, MATIERE, MOYENNE) par exemple est garni après ma première procédure comme ceci :

Code:

---

1 2 3 4 5 6 7

NOM MATIERE MOYENNE   ELEVE1 MATHS 12 ELEVE2 INFO 11 ELEVE1 INFO 16 ELEVE1 FRANCAIS 12

---

Dans ma procedure bilanPivot() j'ai un curseur

Code:

---

1 2	CURSOR monCurseur IS SELECT DISTINCT matiere FROM BILAN ORDER BY matiere;

---

Donc je connais le nombre de matières différente et donc de futur colonne non ?

Ensuite pour chaque matière présente dans mon curseur je l'ajoute à ma requête :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

OPEN monCurseur;   v_req_deb := 'SELECT nom '; -- pas de virgule ici v_req_fin := 'FROM bilan GROUP BY nom'; FETCH monCurseur INTO v_matiere; WHILE(monCurseur%FOUND) LOOP v_req:= v_req || ' ,max(decode(matiere,''' || v_matiere || ''',moyenne)) '; -- une virgule au début, pas à la fin FETCH monCurseur INTO v_matiere; END LOOP; CLOSE monCurseur; v_req_final := v_req_deb || v_req || v_req_fin;

---

Dans la théorie je me retrouverais donc avec une requête final ressemblant à:

Code:

---

1 2 3 4 5 6

SELECT nom ,max(decode(matiere,'MATHS',moyenne)) ,max(decode(matiere,'INFO',moyenne)) ,max(decode(matiere,'FRANCAIS',moyenne)) FROM bilan GROUP BY nom

---

Ma logique n'est pas bonne ?

Merci pour votre aide !

Bonjour,

tu peux essayer un truc comme ca

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

CREATE OR REPLACE package pivot_note as type rc is ref cursor; procedure data ( p_cursor in out rc ); end; /     create or replace package body pivot_note as procedure data(p_cursor in out rc ) is l_stmt long; begin   l_stmt := 'select nom'; for x in ( select distinct MATIERE from BILANCLASSE order by 1 ) loop --- l_stmt := l_stmt || ', max(decode(MATIERE,' || ''''|| x.MATIERE ||''''|| ', MOYENNE )) ' ||'"'|| x.MATIERE||'"'; l_stmt := l_stmt || ', max(decode(MATIERE,' || ''''|| x.MATIERE ||''''|| ', MOYENNE )) ' ; end loop; l_stmt := l_stmt || ' from BILANCLASSE group by NOM '; dbms_output.put_line( l_stmt );   open p_cursor for l_stmt; end;   end; /

---

et en sqlplus

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

> set serverout on lines 200   > variable x refcursor   > exec pivot_note.data(:x); select nom, max(decode(MATIERE,'FRANCAIS', MOYENNE )) , max(decode(MATIERE,'MATHS', MOYENNE )) from BILANCLASSE group by NOM   PL/SQL procedure successfully completed.   > print x;   NOM MAX(DECODE(MATIERE,'FRANCAIS',MOYENNE)) MAX(DECODE(MATIERE,'MATHS',MOYENNE)) -------------------- --------------------------------------- ------------------------------------ ELEVE2 14 8 ELEVE1 11 12   >

---

Citation:

---

Envoyé par fatsora

Bonjour,

tu peux essayer un truc comme ca

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

CREATE OR REPLACE package pivot_note as type rc is ref cursor; procedure data ( p_cursor in out rc ); end; /     create or replace package body pivot_note as procedure data(p_cursor in out rc ) is l_stmt long; begin   l_stmt := 'select nom'; for x in ( select distinct MATIERE from BILANCLASSE order by 1 ) loop --- l_stmt := l_stmt || ', max(decode(MATIERE,' || ''''|| x.MATIERE ||''''|| ', MOYENNE )) ' ||'"'|| x.MATIERE||'"'; l_stmt := l_stmt || ', max(decode(MATIERE,' || ''''|| x.MATIERE ||''''|| ', MOYENNE )) ' ; end loop; l_stmt := l_stmt || ' from BILANCLASSE group by NOM '; dbms_output.put_line( l_stmt );   open p_cursor for l_stmt; end;   end; /

---

et en sqlplus

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

> set serverout on lines 200   > variable x refcursor   > exec pivot_note.data(:x); select nom, max(decode(MATIERE,'FRANCAIS', MOYENNE )) , max(decode(MATIERE,'MATHS', MOYENNE )) from BILANCLASSE group by NOM   PL/SQL procedure successfully completed.   > print x;   NOM MAX(DECODE(MATIERE,'FRANCAIS',MOYENNE)) MAX(DECODE(MATIERE,'MATHS',MOYENNE)) -------------------- --------------------------------------- ------------------------------------ ELEVE2 14 8 ELEVE1 11 12   >

---

---

Bonsoir, j'ai essayé d'insérer le bout de code que tu m'as donné, mais je ne sais pas l'appeler...

Code:

---

1 2 3 4 5 6 7 8 9 10 11

BEGIN pivot_note(); END; /   Erreur : ORA-06550: Ligne 2, colonne 1 : PLS-00221: 'PIVOT_NOTE' n'est pas une procédure ou est indéfini ORA-06550: Ligne 2, colonne 1 : PL/SQL: Statement ignored

---

Citation:

---

Envoyé par GoLDoZ

Si ta table est vide, la requête de ton EXECUTE IMMEDIATE vaut:

Code:

---

SELECT nom, FROM bilan GROUP BY nom

---

Ca plante à cause de la virgule.

Quand on crée une requête dynamiquement, il vaut mieux ajouter les virgules au début de la chaîne ajoutée.

Code:

---

1 2 3 4 5 6 7 8

v_req_deb := 'SELECT nom '; -- pas de virgule ici v_req_fin := 'FROM bilan GROUP BY nom'; FETCH monCurseur INTO v_matiere; WHILE(monCurseur%FOUND) LOOP v_req:= v_req || ' ,max(decode(matiere,''' || v_matiere || ''',moyenne)) '; -- une virgule au début, pas à la fin FETCH monCurseur INTO v_matiere; END LOOP;

---

De plus, ça t'évite de bidouiller la requête avec ça, qui devient inutile:

Code:

---

1 2	v_long:=LENGTH(v_req); v_req:=SUBSTR(v_req,v_long-1,0);

---

---

Merci, j'ai ajouté ton code ma procédure se crée, lorsque je l'appel la procedure j'ai "Instruction traitée." mais aucun affichage...


Merci beaucoup pour votre aide à tous !

Bon, il y a eu une discussion comme celle-la aujourd'hui sur OTN - nombre de colonnes inconnu-, tu peux aller voir la et t'inspirer de l'une des reponses donnees, mais encore une fois, tu n'as pas repondu ce que tu aller faire de la sortie.
Je suis fondamentalement contre ce genre de pratique (nombre de colonnes dynamiques), et je ne comprend pas vraiment pourquoi on en a besoin. En tout cas, Oracle avec SQL et PL/SQL n'est pas designe pour cela.

Nicolas.

Citation:

---

Envoyé par NGasparotto

Bon, il y a eu une discussion comme celle-la aujourd'hui sur OTN - nombre de colonnes inconnu-, tu peux aller voir la et t'inspirer de l'une des reponses donnees, mais encore une fois, tu n'as pas repondu ce que tu aller faire de la sortie.
Je suis fondamentalement contre ce genre de pratique (nombre de colonnes dynamiques), et je ne comprend pas vraiment pourquoi on en a besoin. En tout cas, Oracle avec SQL et PL/SQL n'est pas designe pour cela.

Nicolas.

---

Merci, je vais lire çà !
Je veux juste afficher ma table BILAN en pivot, sachant que je ne connais pas à l'avance le nombre de matières présentent dans ma colonne MATIERE.

Citation:

---

Envoyé par erigoal

Bonsoir, j'ai essayé d'insérer le bout de code que tu m'as donné, mais je ne sais pas l'appeler...

Code:

---

1 2 3 4 5 6 7 8 9 10 11

BEGIN pivot_note(); END; /   Erreur : ORA-06550: Ligne 2, colonne 1 : PLS-00221: 'PIVOT_NOTE' n'est pas une procédure ou est indéfini ORA-06550: Ligne 2, colonne 1 : PL/SQL: Statement ignored

---

Merci, j'ai ajouté ton code ma procédure se crée, lorsque je l'appel la procedure j'ai "Instruction traitée." mais aucun affichage...


Merci beaucoup pour votre aide à tous !

---

Mais la c'est du copier coller !!

pour executer

Code:

---

1 2 3 4

set serverout on   exec pivot_note.DATA(:x);

---

Mais oui , mais il faut lire aussi .....