APSQL

Pivot - Unpivot

Ces 2 opérateurs, spécifiques à SQL Serveur 2005, permettent de transformer les lignes en colonnes et réciproquement. PIVOT et UNPIVOT sont particulièrement utiles lorsque le format d'extraction des informations n'est pas directement compatible avec le format de présentation des données. Par exemple, si l'on souhaite connaitre, par catégories, le nombre d'entraineurs et de sportifs, il est possible de faire la requête suivante:
select dbo.codeCategorie(numero),count(*) from individus where estSportif=1 group by dbo.codeCategorie(numero);
select dbo.codeCategorie(numero),count(*) from individus where estentraineur=1 group by dbo.codeCategorie(numero);
La fonction codeCategorie permet de connaître le code de la cétagorie (Poussins, Benjamins, ..) pour l'individu dont le nom est passé en paramètre.

Exemple de résultat

Cependant ces requêtes ne permettent pas d'avoir une bonne lisibilité du résultat. Il serait préférable d'avoir les résultats sous la forme d'un tableau muni de 2 colonnes (une pour les sportifs et une pour les entraineurs) avec autant de lignes qu'il existe de catégories. La première étape consiste à écrire une requête qui permet de regrouper le type de l'individu (entraineur ou sportif) dans une seule colonne. La requête suivante permet de répondre à ce problème

SELECT numero,cat.code, 'ENT' as est
FROM individus ind, categories cat
WHERE cat.code=dbo.codeCategorie(numero) AND estEntraineur=1
UNION
SELECT numero,cat.code, 'SPO' as est
FROM individus ind, categories cat
WHERE cat.code=dbo.codeCategorie(numero) AND estSportif=1

Mais le résultat n'est pas encore parfait en effet il est préférable d'avoir une seule ligne par catégorie, aussi est il nécessaire de réaliser une opération de calcul d'agrégat par rapport au code catégorie. Cependant pour pouvoir réaliser ce calcul d'agrégat, il est nécessaire au préalable de créer une vue correspondant à la requête exposée ci dessus.

Pour éviter tous ces éléments, relativement lourd, il est possible, en SQL Server 2005 d'utiliser une table CTE et le PIVOT. La table CTE permet de définir une "vue" relative à une requête. Avec la table CTE la durée de vie de la "vue" correspond au temps d'exécution de la requête. La table CTE permet de simplifier l'écriture des requête en limitant, voir en éliminant les sous requêtes. Dans notre exemple, la table CTE permet d'établir pour chaque individu s'il est sportif ou entraineur.

Les informations issues de cette table CTE sont ensuite traitées par la requête qui contient l'instruction PIVOT. L'instruction PIVOT permet de faire un calcul d'agrégat (ici compter) par rapport à des valeurs distinct d'une colonne (ici la colonne est avec les valeurs ENT ou SPO). Le résultat de ce calcul est projeté dans les colonnes correspondant aux différentes valeurs de regroupement (ici ENT et SPO). Ainsi en une seule requête le résultat souhaité est obtenu.

Exemple de PIVOT

UNPIVOT permet de réaliser l'opération inverse, c'est à dire transformer des colonnes en lignes.