Des lignes aux colonnes, un PIVOT suffit pour la 11g ; DECODE en attendant

Selon les fondamentaux des modèles relationnels avec les formes normales, l'information doit être stockée de manière unique et ne doit en aucune sorte être influencée par le rendu.

Un des problèmes courant est le passage du stockage en ligne vers le rendu en colonne.
Depuis la version 11g, Oracle simplifie complètement cette problématique avec l'apparition du mot clé PIVOT. Avant de montrer son utilisation, et pour apprécier le gain, voici dans un premier temps la méthode qu'il fallait utiliser avant cette version ; gràce à la fonction decode (spécifique à Oracle elle aussi, et disponible à partir de la 9i).

La fontion decode


Cette fonction permet en une seule instruction, de comparer une valeur avec une référence, et de retourner une valeur en fonction du test ; c'est en quelques sortes une extension à la fonction nvl (ou coalesce) qui elles ne comparent qu'à null :

select col1, decode(colbool, "vrai", 1, 0) from t1
Retournera un résultset dont la première colonne sera col& et la seconde dépendra de colbool : elle vaudra 1 si colbool vaut "vrai" et 0 dans tous les autres cas.
select col1, decode(coltierce, "un", 1, "deux", 2, "trois", 3, "grand") from t1
Retournera de la même manière, 1 si un, 2, si deux, 3 si trois et grand dans les autres cas (y compris "zero").

Pivot "à l'ancienne"


create parcours (idparcours, lieu, greenfee)
create table trou (trou, par, idparcours, num)


Afin de connaître un parcours et les par de tous ses trous sur une ligne :
SELECT p.*, max(decode(t.num,1,t.par)) AS par_trou1,
MAX(DECODE(t.num,2,t.par)) AS par_trou2,
MAX(DECODE(t.num,3,t.par)) AS par_trou3,
MAX(DECODE(t.num,4,t.par)) AS par_trou4,
MAX(DECODE(t.num,5,t.par)) AS par_trou5,
...
MAX(DECODE(t.num,18,t.par)) AS par_trou18
FROM parcours p INNER JOIN trou t cp ON p.idparcours = t.idparcours


Il est ensuite possible d'utiliser cette requête dans une vue (ou vue matérialisée) si elle est amenée à être souvent appelée que l'on pourra optimiser.

Voir également l'article sur la migration de modèle par les vues

Pivot à partir de 11g


En oracle 11g, on simplifie de la sorte :
SELECT p.*, t.par
FROM parcours p INNER JOIN trou t ON p.idparcours = t.idparcours
PIVOT par FOR t.num IN {1 AS par_trou1,
2 AS par_trou2,
3 AS par_trou3,
4 AS par_trou4,
5 AS par_trou5,
...,
18 AS par_trou18}


Voir également l'usage de la fonction DECODE.




Vous n'avez pas trouver réponse à votre question ? Préciser votre recherche :

Catégories