Discussion :

[alexis trotta]

Bonjour à tous,
peut_on dans la requête suivante supprimer les cases vides entre les valeurs?
Peut importe le nombre de valeurs dans chaque lignes, je souhaiterai les rammener dans les premières colonnes.

Code sql :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SELECT phof_orfa_numero
   , MAX(DECODE (phof_numero_phase,'2',phof_post_code)) AS "1"
   , MAX(DECODE(phof_numero_phase,'9',phof_post_code)) AS "2"
   , MAX(DECODE(phof_numero_phase,'10',phof_post_code)) AS "3"
   , MAX(DECODE(phof_numero_phase,'20',phof_post_code)) AS "4"
   , MAX(DECODE(phof_numero_phase,'25',phof_post_code)) AS "5"
   , MAX(DECODE(phof_numero_phase,'30',phof_post_code)) AS "6"
   , MAX(DECODE(phof_numero_phase,'35',phof_post_code)) AS "7"
   , MAX(DECODE(phof_numero_phase,'40',phof_post_code)) AS "8"
   , MAX(DECODE(phof_numero_phase,'50',phof_post_code)) AS "9"
   , MAX(DECODE(phof_numero_phase,'60',phof_post_code)) AS "10"
   , MAX(DECODE(phof_numero_phase,'70',phof_post_code)) AS "11"
   , MAX(DECODE(phof_numero_phase,'80',phof_post_code)) AS "12"
   , MAX(DECODE(phof_numero_phase,'90',phof_post_code)) AS "13"
   , MAX(DECODE(phof_numero_phase,'100',phof_post_code)) AS "14"
   , MAX(DECODE(phof_numero_phase,'110',phof_post_code)) AS "15"
   , MAX(DECODE(phof_numero_phase,'120',phof_post_code)) AS "16"
   , MAX(DECODE(phof_numero_phase,'130',phof_post_code)) AS "17"
   , MAX(DECODE(phof_numero_phase,'140',phof_post_code)) AS "18"
   , MAX(DECODE(phof_numero_phase,'150',phof_post_code)) AS "19"
   , MAX(DECODE(phof_numero_phase,'160',phof_post_code)) AS "20"
   , MAX(DECODE(phof_numero_phase,'170',phof_post_code)) AS "21"
   , MAX(DECODE(phof_numero_phase,'180',phof_post_code)) AS "22"
   , MAX(DECODE(phof_numero_phase,'190',phof_post_code)) AS "23"
   , MAX(DECODE(phof_numero_phase,'200',phof_post_code)) AS "24"
   , MAX(DECODE(phof_numero_phase,'210',phof_post_code)) AS "25"
   , MAX(DECODE(phof_numero_phase,'220',phof_post_code)) AS "26"
   , MAX(DECODE(phof_numero_phase,'230',phof_post_code)) AS "27"
   , MAX(DECODE(phof_numero_phase,'240',phof_post_code)) AS "28"
   , MAX(DECODE(phof_numero_phase,'250',phof_post_code)) AS "29"
   , MAX(DECODE(phof_numero_phase,'260',phof_post_code)) AS "30"
   , MAX(DECODE(phof_numero_phase,'270',phof_post_code)) AS "31"
   , MAX(DECODE(phof_numero_phase,'280',phof_post_code)) AS "32"
   , MAX(DECODE(phof_numero_phase,'290',phof_post_code)) AS "33"
   , MAX(DECODE(phof_numero_phase,'300',phof_post_code)) AS "34"
   , MAX(DECODE(phof_numero_phase,'310',phof_post_code)) AS "35"
   , MAX(DECODE(phof_numero_phase,'400',phof_post_code)) AS "36"
   , MAX(DECODE(phof_numero_phase,'450',phof_post_code)) AS "37"
   , MAX(DECODE(phof_numero_phase,'490',phof_post_code)) AS "38"
   , MAX(DECODE(phof_numero_phase,'491',phof_post_code)) AS "39"
   , MAX(DECODE(phof_numero_phase,'492',phof_post_code)) AS "40"
   , MAX(DECODE(phof_numero_phase,'493',phof_post_code)) AS "41"
   , MAX(DECODE(phof_numero_phase,'494',phof_post_code)) AS "42"
   , MAX(DECODE(phof_numero_phase,'495',phof_post_code)) AS "43"
   , MAX(DECODE(phof_numero_phase,'496',phof_post_code)) AS "44"
   , MAX(DECODE(phof_numero_phase,'497',phof_post_code)) AS "45"
   , MAX(DECODE(phof_numero_phase,'498',phof_post_code)) AS "46"
   , MAX(DECODE(phof_numero_phase,'499',phof_post_code)) AS "47"
   , MAX(DECODE(phof_numero_phase,'500',phof_post_code)) AS "48"
   , MAX(DECODE(phof_numero_phase,'510',phof_post_code)) AS "49"  
    FROM phase_of
    WHERE phof_statut = 'L'
    AND phof_orfa_numero = '201'
    GROUP BY phof_orfa_numero

Merci d'avance.

[Olivier Dufour]

Bonjour,

Cf ma réponse dans : http://www.developpez.net/forums/d13...onnees-select/

Cdlt,
OD

[alexis trotta]

Bonjour et merci pour votre réponse.

facts est un identificateur non valide quand je l'exécute dans Toad.

AT

[Olivier Dufour]

Re-bonjour,

Il ne faut pas reprendre ma réponse telle que, mais l'adapter à votre cas ...

Cdlt,
OD

[alexis trotta]

Oui effectivement, j'avais commis des erreurs dans la transposition.
Voici ci_dessous la requête définitive qui marche.

Code sql :

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
29
30
31
32
33
34
35
36
37
38
39
40
SELECT orfa_arti_code "art"
     , phof_orfa_numero "of"
     , orfa_dt_besoin "Besoin"
     , orfa_designation "Désign"
     , orfa_qte_lance "Qté"
     , max(case when r=1 then poste_ end) AS OP1
     , max(case when r=2 then poste_ end) AS OP2
     , max(case when r=3 then poste_ end) AS OP3
     , max(case when r=4 then poste_ end) AS OP4
     , max(case when r=5 then poste_ end) AS OP5
     , max(case when r=6 then poste_ end) AS OP6
     , max(case when r=7 then poste_ end) AS OP7
     , max(case when r=8 then poste_ end) AS OP8
     , max(case when r=9 then poste_ end) AS OP9
     , max(case when r=10 then poste_ end) AS OP10
     , max(case when r=11 then poste_ end) AS OP11
     , max(case when r=12 then poste_ end) AS OP12
     , max(case when r=13 then poste_ end) AS OP13
     , max(case when r=14 then poste_ end) AS OP14
     , max(case when r=15 then poste_ end) AS OP15
     , max(case when r=16 then poste_ end) AS OP16
     , max(case when r=17 then poste_ end) AS OP17
     , max(case when r=18 then poste_ end) AS OP18
     , max(case when r=19 then poste_ end) AS OP19
     , max(case when r=20 then poste_ end) AS OP20
     , max(case when r=21 then poste_ end) AS OP21
     , max(case when r=22 then poste_ end) AS OP22
     , max(case when r=23 then poste_ end) AS OP23
     , max(case when r=24 then poste_ end) AS OP24
FROM (SELECT phof_orfa_numero, orfa_arti_code, orfa_dt_besoin, orfa_designation, orfa_qte_lance
           , rank() OVER (PARTITION BY  phof_orfa_numero ORDER BY phof_numero_phase ) AS r
           , phof_post_code AS poste_
        FROM phase_of, ordre_fabrication
            WHERE phof_statut = 'L'
            AND phof_orfa_numero = orfa_numero
            AND phof_orfa_numero not in ( '203','207','210','213','218','221','238','249','263',
            '272','8771','8808','8931','10918','11156','11223','17469','17470','17471')--of impro
     )
GROUP BY phof_orfa_numero, orfa_arti_code, orfa_dt_besoin, orfa_designation, orfa_qte_lance
ORDER BY 1,2

Merci encore,

AT