Requête Pivot sous Oracle

Oracle_logo

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

2 commentaires

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Captcha *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.