Discussion :

[CharlEm]

Bonjour,

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

en ACCESS j'ai :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[CharlEm]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[samsan]

Bonjour,

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

Merci

[al1_24]

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

[samsan]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

[SQLpro]

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 +