Depuis la version 11g, Oracle propose l’opérateur PIVOT. Celui-ci permet d'agréger des données contenues dans des lignes d'enregistrement, et de les convertir en colonnes.
Exemple :
Imaginons une table "COLLABORATEUR" contenant, pour chaque collaborateur, son département et sa fonction :
| NOM | DPT | FONCTION |
| Dupont | 69 | Directeur d'Agence |
| Ferrat | 42 | Technicien |
| Duchemin | 69 | Technicien |
| Paul | 69 | Technicien |
| Durant | 42 | Assistant |
| Dupond | 38 | Assistant |
| … | … | … |
Objectif :
On souhaite connaître, sous forme “tabulaire”, le nombre de collaborateurs par fonction et par département.
Une requête SQL de type "Pivot" va nous permettre d'obtenir une colonne par département, contenant le nombre de collaborateurs pour chaque fonction :
SELECT *
FROM (SELECT FONCTION, DPT FROM COLLABORATEUR)
PIVOT (count(DPT) for DPT IN ('38', '42', '69'));
Le résultat affiche bien la répartition des fonctions pour les 3 départements demandés, en créant une colonne par département :
| FONCTION | '38' | '42' | '69' |
| Directeur d'Agence |
0 |
0 |
1 |
| Technicien |
0 |
1 |
2 |
| Assistant |
1 |
1 |
0 |
| … |
… |
... |
… |
Alias :
On notera que si les colonnes de département apparaissent bien, elles sont nommées par le critère de sélection ('38','42','69').
Il est possible de les renommer en utilisant des alias :
SELECT *
FROM (SELECT FONCTION, DPT FROM COLLABORATEUR)
PIVOT (count(DPT) for DPT IN ('38' as Isere, '42' as Loire, '69' as Rhone));
Dans le résultat obtenu, les colonnes sont alors nommées de manière plus explicite :
| FONCTION | ISERE | LOIRE | RHONE |
| Directeur d'Agence |
0 |
0 |
1 |
| Technicien |
0 |
1 |
2 |
| Assistant |
1 |
1 |
0 |
| … |
… |
... |
… |
A noter :
- Le contenu du pivot est forcément une fonction d'agrégation : Sum, Count, Avg...
- La clause IN permettant de filtrer sur les départements est obligatoire. Il n'est donc pas possible d'obtenir en colonne l'ensemble des départements, à moins d'utiliser le 'XML pivoting' (l'ajout du mot clé 'PIVOT XML' est une variante de syntaxe qui retourne le jeu de résultats au format XML).
- L’opération inverse est également possible : l’opérateur UNPIVOT permet d’éclater des données contenues en colonnes sur plusieurs lignes.
- L’opérateur PIVOT existe aussi sous SQL-Server. Pour plus d’information, consultez : https://msdn.microsoft.com/en-us/library/ms177410(SQL.105).aspx
Merci Sebastien. Je suis tellement vieux en Oracle que je ne connais pas les nouvelles fonctions…. je viens de decouvrir le merge deja :p.
Merci pour cette mise à jour des connaissances sur Oracle car je dois exploiter une base de données hotel.