Cross Tab queries en Oracle 10

Cross-tab queries ou Pivot en Oracle 10g.

Je sais qu'Oracle 11 introduit la notion de Pivot table qui permette de créer des cross tab queris (affichage d'une réponse classée dans un tableau par valeur d'une variable catégorielle). Les adorateurs de SAS connaissent cela via la procedure PROC TABLE qui peut faire un tantinet plus qu'afficher un simple tableau, puisque l'on peut aussi calculer un test de Khi carré mais bon même si mon blog est dédié au stat là n'est pas le sujet du billet d'aujourd'hui.
Par contre les utilisateurs d'Oracle 10 ne peuvent pas bénéficier de cette avancée (bien fait...). Il faut donc trouver un palliatif.
Je prends un cas usuel, nous avons des sujets et ces sujets on différentes propriétés 3 en l'occurrences dans notre exemple. Chaque sujet possède une valeur par propriété. En gros j'ai deux tables, une table sujet et une table propriété.

Une jointure entre les tables nous donne le tableau suivant: V1 et V2 sont des variables numériques, V3 est une variable de type texte.

Sujets
Sujet DOB
1 15/01/2000
2 1/1/1992

Variables
Sujet Nom Valeur
1 V1 15
1 V2 2
1 V3 test
1 V1 17
1 V2 3
1 V3 subj2

La jointure des tables sujets - variables nous donnent ceci

Sujet DOB Nom Valeur
1 15/01/2000 V1 15
1 15/01/2000 V2 2
1 15/01/2000 V3 test
2 1/1/1992 V1 17
2 1/1/1992 V2 3
2 1/1/1992 V3 subj2

Cette approche n'est pas très commode et souffre même d'un certain gigantisme tabulatoire.

Une approche simplificatrice consiste à utiliser une combinaison judicieuse de MAX et de DECODE afin de produire un tableau de type
Sujet V1 V2 V3
1 15 2 test
2 17 3 subj2

Ce que permet le bout de SQL suivant:
select Sujet, MAX(DECODE(Nom, 'V1', VALEUR, NULL)) IV1,
MAX(DECODE(Nom, 'V2', Valeur, NULL)) IV2,
MAX(DECODE(Nom, 'V3', Valeur, NULL)) IV3
FROM Variables
group by Sujet
order by Sujet;

Explication rapide : le DECODE affiche
Select Sujet, DECODE(Nom, 'V1', VALEUR, NULL) IV1,
DECODE(Nom, 'V2', Valeur, NULL) IV2,
DECODE(Nom, 'V3', Valeur, NULL) IV3
FROM Variables

Nous donne un tableau constitué du sujet auquel on concatène chaque valeur de V1, V2, V3. si la variable vaut V1 et qu'elle a une valeur alors sa valeur est affichée, sinon on affiche NULL

Cela nous donne

Sujet V1 V2 V3
1 15 null null
1 null 2 null
1 null null test
2 17 null null
2 null 3 null
2 null null subj2

On remarque que par sujet on obtient une matrice diagonale. L'utilisation d'un regroupement sur MAX permet d'éliminer les valeurs nulles.

Vous remarquerez qu'Oracle a une drôle d'interprétation de la nullité. Nous savons en effet que null signifie que l'on ne connaît pas la valeur. Si Alain à un age de 30 ans et Philippe un age inconnu que vaut le MAX(Age) entre Alain et Philippe. La réponse est normalement je ne sais pas. Pour Oracle la réponse est Alain. C'est assez divinatoire (sans mauvais jeu de mot) comme approche mais ça marche pour notre exemple, nous ne nous en plaindrons donc pas !