Discussion :

[bstevy]

Bonjour,

Je suis en train d'essayer de me dépatouiller avec la fonction PIVOT d'oracle, et j'avoue que le fonctionnement est un peu obscure pour moi...

J'essaye de réaliser un double pivot, mais je ne vois pas trop comment l'ecrire.
Je ne sais d'ailleur pas si c'est faisable avec la fonction PIVOT, et dans ce cas, je passerai sans doute par des fonctions simples d'aggregation, mais je voudrais votre avis si possible.



Voici un jeu de données :

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
create table table_test (
contrat integer,
type varchar(2),
plan integer,
valeur integer,
flag varchar(1)
);
 
 
insert into table_test values (1	,'FC'	,1	,2	,'Y');
insert into table_test values (1	,'FC'	,2	,99	,'N');
insert into table_test values (1	,'FC'	,3	,29	,'Y');
insert into table_test values (1	,'NB'	,1	,22	,'N');
insert into table_test values (1	,'NB'	,2	,35	,'Y');
insert into table_test values (1	,'NB'	,3	,35	,'N');
insert into table_test values (2	,'FC'	,1	,4	,'Y');
insert into table_test values (2	,'FC'	,2	,90	,'Y');
insert into table_test values (2	,'FC'	,3	,73	,'N');
insert into table_test values (2	,'NB'	,1	,19	,'N');
insert into table_test values (2	,'NB'	,2	,41	,'N');
insert into table_test values (2	,'NB'	,3	,57	,'Y');
commit;

CONTRAT	TYPE	PLAN	VALEUR	FLAG
1	FC	1	2	Y
1	FC	2	99	N
1	FC	3	29	Y
1	NB	1	22	N
1	NB	2	35	Y
1	NB	3	35	N
2	FC	1	4	Y
2	FC	2	90	Y
2	FC	3	73	N
2	NB	1	19	N
2	NB	2	41	N
2	NB	3	57	Y

Je voudrais réaliser mon PIVOT à la fois sur le plan et sur le type...
mais j'ai l'impression que la fonction PIVOT ne prend qu'un axe à la fois.
Comment je peux faire pour avoir les deux ?


Le résultat devant être comme suit :

Contrat

valeur_FC_1

valeur_FC_2

valeur_FC_3

valeur_NB_1

valeur_NB_2

valeur_NB_3

flag_FC_1

flag_FC_2

flag_FC_3

flag_NB_1

flag_NB_2

flag_NB_3

1

2

99

29

22

35

35

Y

N

Y

N

Y

N

2

4

90

73

19

41

57

Y

Y

N

N

N

Y

J'en suis là :

Code :

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

1
2
3
select *
from   (    select CONTRAT, TYPE, PLAN, VALEUR, FLAG from table_test )
pivot (max(VALEUR) as sum_VALEUR, max(FLAG) as max_flag  for (PLAN) in ('1' as a,'2' as b,'3' as c)  )

et j'arrive pas à "pivoter" d'avantage...


Vous pourriez me donner un coup de main, svp ?


Merci d'avance,


Steven

[Waldar]

Je pense qu'on est dans un cas trop compliqué pour PIVOT.
On peut les imbriquer, mais là rapidement je ne vois pas de solution plus simple et lisible que le classique case group by.

[Waldar]

En fait si :

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
29
30
31
32
33
with cte_matable as
(
select contrat
     , type || '_' || to_char(plan) as axe
     , valeur
     , flag
  from table_test
)
select contrat
     , fc_1_valeur as valeur_fc_1
     , fc_2_valeur as valeur_fc_2
     , fc_3_valeur as valeur_fc_3
     , nb_1_valeur as valeur_nb_1
     , nb_2_valeur as valeur_nb_2
     , nb_3_valeur as valeur_nb_3
     , fc_1_flag   as flag_fc_1
     , fc_2_flag   as flag_fc_2
     , fc_3_flag   as flag_fc_3
     , nb_1_flag   as flag_nb_1
     , nb_2_flag   as flag_nb_2
     , nb_3_flag   as flag_nb_3
  from cte_matable
 pivot ( max(valeur) as valeur
       , max(flag)   as flag
         for (axe) in ( 'FC_1' as fc_1, 'FC_2' as fc_2, 'FC_3' as fc_3
                      , 'NB_1' as nb_1, 'NB_2' as nb_2, 'NB_3' as nb_3
                      )
       );
 
   CONTRAT VALEUR_FC_1 VALEUR_FC_2 VALEUR_FC_3 VALEUR_NB_1 VALEUR_NB_2 VALEUR_NB_3 FLAG_FC_1 FLAG_FC_2 FLAG_FC_3 FLAG_NB_1 FLAG_NB_2 FLAG_NB_3
---------- ----------- ----------- ----------- ----------- ----------- ----------- --------- --------- --------- --------- --------- ---------
         1           2          99          29          22          35          35 Y         N         Y         N         Y         N         
         2           4          90          73          19          41          57 Y         Y         N         N         N         Y

Mais bon, ce n'est pas beaucoup plus simple à lire que :

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
  select contrat
       , max(case when type = 'FC' and plan = 1 then valeur end) as valeur_fc_1
       , max(case when type = 'FC' and plan = 2 then valeur end) as valeur_fc_2
       , max(case when type = 'FC' and plan = 3 then valeur end) as valeur_fc_3
       , max(case when type = 'NB' and plan = 1 then valeur end) as valeur_nb_1
       , max(case when type = 'NB' and plan = 2 then valeur end) as valeur_nb_2
       , max(case when type = 'NB' and plan = 3 then valeur end) as valeur_nb_3
       , max(case when type = 'FC' and plan = 1 then flag   end) as flag_fc_1
       , max(case when type = 'FC' and plan = 2 then flag   end) as flag_fc_2
       , max(case when type = 'FC' and plan = 3 then flag   end) as flag_fc_3
       , max(case when type = 'NB' and plan = 1 then flag   end) as flag_nb_1
       , max(case when type = 'NB' and plan = 2 then flag   end) as flag_nb_2
       , max(case when type = 'NB' and plan = 3 then flag   end) as flag_nb_3
    from table_test
group by contrat;

[bstevy]

Bien vu le coup de la cte

Je teste sur ma vraie table, plus grosse avec plus de colonne, voir ce que ca donne en vrai.
Je vais comparer les perfs surtout, c'est ce que je cherche à maximiser et je vous dirai ce qu'il en est.

Merci en tout cas



EDIT : OK, j'ai fait les tests que j'avais à faire... mais effectivement, ca change pas grand chose en terme de temps d'execution entre cette version et le classic aggregate.
Le plan du PIVOT est même un peu plus couteux, donc je pense que je vais rester sur mon aggregate
Merci pour cette solution tt de même, c'est toujours bon à prendre