Pivot en Access = quoi en Oracle ?

Bonjour,

Je dois migrer des requêtes faites en ACCESS en ORACLE mais je n'y arrive pas.

en ACCESS j'ai :

Code:

---

1 2 3 4 5 6

TRANSFORM Sum(DATA.ITEM_VALUE) AS SommeDeITEM_VALUE SELECT DATA.US, DATA.NADM, DATA.PAT_LASTNAME, DATA.PAT_FIRSTNAME, DATA.PAT_BIRTHDAY, DATA.EP_DATE, DATA.EP_STARTHRE, DATA.EP_ENDHRE FROM DATA GROUP BY DATA.US, DATA.NADM, DATA.PAT_LASTNAME, DATA.PAT_FIRSTNAME, DATA.PAT_BIRTHDAY, DATA.EP_DATE, DATA.EP_STARTHRE, DATA.EP_ENDHRE ORDER BY DATA.US, DATA.PAT_LASTNAME, DATA.EP_DATE, DATA.EP_STARTHRE PIVOT DATA.ITEM_CODE_ID;

---

En Oracle ça doit quoi un truc pareil ?
PIVOT ça n'existe pas,
TRANSFORM oracle n'aime pas non plus ... quelle gallère le code généré par Access

Merci tout le monde

Pour le moment j'ai fais ceci mais c'est très indigeste comme code et c'est terriblement lent.

-- Ok ça marche pour un amsi c'est trop lent

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

select distinct gre.*, (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'A100') "A100", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B100') "B100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B210') "B210",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B220') "B220",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B230') "B230",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B240') "B240",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B250') "B250",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B300') "B300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B410') "B410",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B420') "B420",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B430') "B430",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B440') "B440",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B500') "B500",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'B600') "B600", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'C110') "C110",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'C120') "C120",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'C200') "C200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'C400') "C400", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'D110') "D110",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'D120') "D120",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'D130') "D130",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'D200') "D200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'D300') "D300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'D400') "D400",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'D500') "D500", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'E100') "E100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'E200') "E200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'E300') "E300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'E400') "E400", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'F110') "F110",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'F120') "F120",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'F200') "F200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'F300') "F300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'F400') "F400",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'F500') "F500", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'G100') "G100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'G200') "G200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'G300') "G300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'G400') "G400",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'G500') "G500", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'H100') "H100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'H200') "H200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'H300') "H300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'H400') "H400",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'H500') "H500", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'I100') "I100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'I200') "I200", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'K100') "K100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'K200') "K200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'K300') "K300", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'L100') "L100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'L200') "L200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'L300') "L300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'L400') "L400",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'L500') "L500", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'M100') "M100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'N100') "N100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'N200') "N200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'N300') "N300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'N400') "N400",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'N500') "N500",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'N600') "N600",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'N700') "N700", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'O100') "O100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'O200') "O200", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'P100') "P100", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'Q100') "Q100", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'R110') "R110",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'R120') "R120",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'R130') "R130", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'S100') "S100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'S200') "S200", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'V100') "V100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'V200') "V200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'V300') "V300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'V400') "V400",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'V500') "V500",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'V600') "V600",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'V700') "V700", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'W100') "W100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'W200') "W200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'W300') "W300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'W400') "W400",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'W500') "W500", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'X100') "X100", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'Y100') "Y100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'Y200') "Y200", (select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'Z100') "Z100",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'Z200') "Z200",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'Z300') "Z300",(select value from mtgd_rimstats where numepisode=mr.numepisode and coderim = 'Z400') "Z400" from glit_rim_episode gre, mtgd_rimstats mr where gre.numepisode = mr.numepisode and gre.numepisode = 93411

---

Bonjour,

J'ai le même besoin. Tu as trouvé qqchose

Merci

TRANSFORM ... PIVOT est une spécificité d'Access.
Il n'existe pas d'équivalent ni dans Oracle, ni dans la norme du langage SQL

Voilà comment je me suis débrouillé en SQL. En sachant que chaque devise n'a qu'un taux journalier, j'utilise MAX.

Requête standard sur ma table des devises

Code:

---

1 2 3 4 5 6 7

SELECT T$STDT as Dte, T$CCUR as Devise, T$RATP as Taux FROM BAANDB.TTCCRI700500 ORDER BY T$STDT DESC;

---

Résultats :

Code:

---

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

DTE DEVISE TAUX ------------------- --------- ---------- 21/05/2008 00:00:00 USD 1,5577 21/05/2008 00:00:00 JPY 162,04 20/05/2008 00:00:00 USD 1,5577 20/05/2008 00:00:00 JPY 162,04 19/05/2008 00:00:00 USD 1,5439 15/05/2008 00:00:00 USD 1,5439 15/05/2008 00:00:00 JPY 162,48 14/05/2008 00:00:00 USD 1,5473 14/05/2008 00:00:00 JPY 161,71

---

Requête "pseudo" pivot:

Code:

---

1 2 3 4 5 6 7 8

SELECT to_char(T$STDT,'DD/MM/YYYY') as Dte, MAX(DECODE(T$CCUR,'USD',T$RATP,NULL)) as USD, MAX(DECODE(T$CCUR,'JPY',T$RATP,NULL)) as JPY FROM BAANDB.TTCCRI700500 GROUP BY T$STDT ORDER BY T$STDT DESC;

---

Résultats :

Code:

---

1 2 3 4 5 6 7 8

DTE USD JPY ---------- -------- ---------- 21/05/2008 1,5577 162,04 20/05/2008 1,5577 162,04 19/05/2008 1,5439 (null) 15/05/2008 1,5439 162,48 14/05/2008 1,5473 161,71

---

Qu'en pensez-vous ? Y a t'il plus "classieux" à faire ?

PIVOT est une abération d'Access et n'existe heureusement pas dans le langage SQL, car il s'agit d'une clause cosmétique....
C'est à la couche présentation d'assumer cette transformation qui ne change rien aux données et met à genou les serveurs SQL !

Lisez les articles que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/sqlaz/erreurs/#L9
http://sqlpro.developpez.com/SQL_Server_2K5/N1.php
§ 1.8 Opérateur PIVOT / UNPIVOT

A +