[SQL] PIVOT en T-SQL une instruction magique, même si pas très standard…

L’instruction PIVOT est l’amie des fans de Tableaux Croisés Dynamiques sous Excel. Elle est en fait assez méconnue – en dehors de nous autres Consultants BI – mais peut servir à faire pas mal de choses.

Le but de l’affaire est de transformer une colonne en ligne, dit autrement en dimension posée sur l’axe COLUMNS et un indicateur numérique en mesure. Par exemple imaginons que l’on possède la liste des ventes par vendeur à destination de chaque pays:

Vendeur Pays Nb Ventes
Michel France 1000
Michel Allemagne 500
André Italie 2000
Gilles France 1500

On peut vouloir exécuter tout un type de rapports, sous forme de TCD, qui ressemblent habituellement à ceux sortis d’un cube OLAP: voir la somme des ventes par vendeur (axe vendeur en ligne) et par géographie:

Vendeur/Géographie France Allemagne Italie
Michel 1000 500 NULL
André NULL NULL 2000
Gilles 1500 NULL NULL

Ou plus simplement la moyenne des volumes de ventes par pays:

Pays France Allemagne Italie
Moy. Ventes 1250 500 2000

Tout cela peut se faire simplement avec l’instruction PIVOT. Celle ci traite les colonnes d’entrée en 3 catégories:

  1. La mesure, qui dispose d’un mode d’aggrégation
  2. La liste des membres d’une colonne a poser en dimension
  3. La liste des colonnes conservées telles-quelles (si on en garde une seule, cela fait une autre dimension en lignes)

Pour la première opération on la méthode est donc celle-ci:

  1. On récupère d’abord les valeurs nécessaires incluses dans une sous table
  2. Puis on exécute l’instruction PIVOT qui spécifie une aggrégation pour la mesure choisie ainsi que les membres de la dimension à mettre en colonnes (i.e. à extraire des valeurs d’une colonne)
  3. Enfin on fait un SELECT sur le tout, en séléctionnant les colonnes conservées comme dimension en ligne

SELECT Vendeur, [Allemagne], [France], [Italie]
FROM
(
SELECT Vendeur,Pays,NbVentes
FROM dbo.TestPivot
) AS t
PIVOT
(
SUM(NbVentes)
FOR Pays IN ([Allemagne],[France],[Italie])
) AS p

Une méthode identique est utilisée pour le deuxième exemple, avec aucune colonne conservée comme dimension en ligne, et un mode d’aggréagation AVG pour la mesure.

SELECT [Allemagne],[France],[Italie]
FROM
(
SELECT Pays,NbVentes
FROM dbo.TestPivot
) AS t
PIVOT
(
AVG(NbVentes)
FOR Pays IN ([Allemagne],[France],[Italie])
) AS p

L’inconvénient de tout cela est le côté fastidieux d’énumérer les membres pour la dimension à pivoter: j’ai donc écrit la PS « SmartPivot » qui se sert de curseurs et de DSQLL pour forger le PIVOT. Elle s’utilise comme cela pour les deux exemples, en spécificant juste les 3 types d’usage pour les colonnes concernées, et le mode d’aggrégation de la mesure:

EXEC [dbo].[SmartPivot]
@TableName = N’dbo.TestPivot’,
@LabelColumn = N’Pays’,
@ValueColumn = N’NbVentes’,
@FixedColumns = N’Vendeur’,
@Aggregate = N’SUM’

EXEC [dbo].[SmartPivot]
@TableName = N’dbo.TestPivot’,
@LabelColumn = N’Pays’,
@ValueColumn = N’NbVentes’,
@FixedColumns = N »,
@Aggregate = N’AVG’

La PS SmartPivot est téléchargeable ici sur mon SkyDrive.

A bientôt!

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s