Réaliser un double pivot

Version imprimable

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:

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:

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

22/05/2015, 19h05
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.

En fait si :

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
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:

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;

25/05/2015, 02h08
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 ;)

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