[SQL Server] T-SQL : PIVOT/UNPIVOT, ou une autre façon d’avoir des requêtes pivots simplifiées

Ce billet aborde l’utilisation des opérateurs PIVOT et UNPIVOT en T-SQL.

Présentation de PIVOT

PIVOT est un opérateur de relation apparu à partir de SQL Server 2005, et permettant d’effectuer des opérations de transformation de champs en colonnes.

Son utilité est telle qu’avant SQL Server 2005, la solution pour pivoter des champs de valeurs en colonne était d’écrire des requêtes souvent complexes utilisant – généralement – des expressions CASE.

Voici la syntaxe de l’utilisation de PIVOT :

SELECT <colonne non-pivotée>,
 [première colonne pivotée] AS <nom de colonne>,
 [seconde colonne pivotée] AS <nom de colonne>,
 ...
 [dernière colonne pivotée] AS <nom de colonne>
FROM
 (<Requête SELECT qui produit les données>) AS <alias pour la requête source>
 PIVOT
 (<fonction d'agrégation>(<colonne qui est regroupée>)
 FOR [<colonne qui contient les valeurs qui deviendront des en-tête de colonne>]
 IN ( [première colonne pivotée], [seconde colonne pivotée],
 ... [dernière colonne pivotée])) AS <alias pour le tableau croisé dynamique>
<clause ORDER BY facultative> ;

Exemple d’utilisation de PIVOT

Soit une table ProductOrder contenant la liste des commandes journalières, dont voici sa syntaxe :

  • De création :
CREATE TABLE ProductOrder(Product VARCHAR(20), Quantity INT, IncomeDay VARCHAR(10))
GO
  • D’insertion d’enregistrements :
INSERT INTO ProductOrder(Product, Quantity, IncomeDay)
 SELECT 'Rice',3,'Tuesday'
 UNION ALL
 SELECT 'Milk',1,'Wednesday'
 UNION ALL
 SELECT 'Spaghetti',10,'Saturday'
 UNION ALL
 SELECT 'Ice cream',24,'Tuesday'
 UNION ALL
 SELECT 'Rice',3,'Friday'
 UNION ALL
 SELECT 'Soda',6,'Monday'
 UNION ALL
 SELECT 'Meat',1,'Monday'
 UNION ALL
 SELECT 'Meat',7,'Thursday'
 UNION ALL
 SELECT 'Fish',1,'Sunday'
 UNION ALL
 SELECT 'Salad',11,'Friday'
GO

Au niveau de la représentation du contenu des résultats (via un simple SELECT), cela donnerait :


Voici ci-dessous quelques exemples très simples d’utilisation de PIVOT :

  • Détermination de la quantité de ventes journalières par produit :
SELECT Product, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
FROM (SELECT IncomeDay, Product, Quantity
 FROM ProductOrder) o
 PIVOT (SUM(Quantity)
 FOR IncomeDay IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS PvtIncomeDay
GO

/* Ou sans table dérivée
SELECT Product, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
FROM ProductOrder
 PIVOT (SUM (Quantity) FOR IncomeDay IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS PvtIncomeDay
GO
*/

Ce qui donnerait :


  • Détermination de la quantité de produits vendus par jour :
SELECT IncomeDay, [Rice], [Milk], [Spaghetti], [Ice cream], [Meat], [Fish], [Salad], [Soda]
FROM (SELECT IncomeDay, Product, Quantity
 FROM ProductOrder) o
 PIVOT (SUM(Quantity)
 FOR Product IN ([Rice], [Milk], [Spaghetti], [Ice cream], [Meat], [Fish], [Salad], [Soda])) AS PvtProduct
GO

/* Ou sans table dérivée
SELECT IncomeDay, [Rice], [Milk], [Spaghetti], [Ice cream], [Meat], [Fish], [Salad], [Soda]
FROM ProductOrder
 PIVOT (SUM (Quantity) FOR Product IN ([Rice], [Milk], [Spaghetti], [Ice cream], [Meat], [Fish], [Salad], [Soda])) AS PvtProduct
GO
*/

Ce qui donnerait :


Quid d’UNPIVOT ?

L’opérateur UNPIVOT produit des résultats inverses à ceux de PIVOT. Il permet de transformer des colonnes en champs de valeurs.

Supposons que l’on dispose d’une table ProductOrder constituée d’une colonne Product et des colonnes représentant des jours de la semaine :

CREATE TABLE ProductOrder(Product VARCHAR(20), Monday VARCHAR(10), Tuesday VARCHAR(10), Wednesday VARCHAR(10), Thursday VARCHAR(10), Friday VARCHAR(10), Saturday VARCHAR(10), Sunday VARCHAR(10))
GO

Et supposons également que niveau contenu, cela donne :

INSERT INTO ProductOrder(Product, Quantity, IncomeDay)
 SELECT 'Rice',3,'Tuesday'
 UNION ALL
 SELECT 'Milk',1,'Wednesday'
 UNION ALL
 SELECT 'Spaghetti',10,'Saturday'
 UNION ALL
 SELECT 'Ice cream',24,'Tuesday'
 UNION ALL
 SELECT 'Rice',3,'Friday'
 UNION ALL
 SELECT 'Soda',6,'Monday'
 UNION ALL
 SELECT 'Meat',1,'Monday'
 UNION ALL
 SELECT 'Meat',7,'Thursday'
 UNION ALL
 SELECT 'Fish',1,'Tuesday'
 UNION ALL
 SELECT 'Salad',11,'Friday'
GO


Voici un exemple d’utilisation d’UNPIVOT permettant de lister les quantités de produits au sein d’une colonne Quantity et les jours au sein d’une colonne IncomeDay :

SELECT Product, Quantity, IncomeDay
FROM (SELECT Product, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
 FROM ProductOrder) po
 UNPIVOT (Quantity
 FOR IncomeDay IN([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS PvtIncomeDay
GO

/** Ou sans table dérivée
SELECT Product, Quantity, IncomeDay
FROM ProductOrder
 UNPIVOT (Quantity FOR IncomeDay IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS UnPvtIncomeDay
GO
**/

Le résultat (screenshot tronqué pour des raisons de lisibilité) sera le suivant :

Pour aller plus loin…

Plus haut, il est indiqué que l’opérateur UNPIVOT effectue l’opération inverse d’un PIVOT. Mais est-ce pour autant qu’il soit son exact opposé ? Pas forcément, car tout dépend de la manière dont les données sont organisées…

Cas 1 : UNPIVOT effectue l’opération inverse d’un PIVOT.

Pour être factuel, reprenons notre exemple d’utilisation de PIVOT avec la table ProductOrder qui liste la quantité de produits vendus par jour, et surtout le premier test d’utilisation (quantité de produits vendus par jour) :

  • Rappel du résultat initial pré-PIVOT (simple SELECT)
    :


  • Rappel du résultat post-PIVOT (et de la requête associée) :
SELECT Product, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
FROM (SELECT IncomeDay, Quantity
 FROM ProductOrder) po
 PIVOT(SUM(Quantity)
 FOR IncomeDay IN([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS PvtIncomeDay
GO


  • Application d’UNPIVOT :
SELECT Product, Quantity, IncomeDay
FROM(SELECT Product, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
 FROM (SELECT IncomeDay, Product, Quantity
 FROM ProductOrder) po
 PIVOT(SUM(Quantity)
 FOR IncomeDay IN([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS PvtIncomeDay) pvt
 UNPIVOT(Quantity
 FOR IncomeDay IN([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS UnPvtIncomeDay
GO

Ce qui donnera :


Cas 2 : UNPIVOT n’effectue pas l’opération inverse d’un PIVOT.

Reprenons tout l’exemple du cas 1, en prenant bien soin d’omettre Product dans la liste de sélections de chaque requête :

  • Contenu initial :
SELECT Quantity, IncomeDay
FROM ProductOrder
GO


  • Après PIVOT :
SELECT Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
FROM (SELECT IncomeDay, Quantity
 FROM ProductOrder) po
 PIVOT(SUM(Quantity)
 FOR IncomeDay IN([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS PvtIncomeDay
GO


  • Après application d’UNPIVOT :
SELECT Quantity, IncomeDay
FROM(SELECT Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
 FROM (SELECT IncomeDay, Quantity
 FROM ProductOrder) po
 PIVOT(SUM (Quantity)
 FOR IncomeDay IN([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS PvtIncomeDay) pvt
 UNPIVOT(Quantity
 FOR IncomeDay IN([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) AS UnPvtIncomeDay
GO


Comme on peut le constater ci-dessus, le résultat post-UNPIVOT d’un PIVOT ne donne pas la même chose que le contenu initial pré-PIVOT (les quantités de Monday, Tuesday et Friday ont été fusionnées).     Cela s’explique par le fait qu’à partir du moment où l’opérateur UNPIVOT travaille sur les résultats fusionnés (suite à une agrégation SUM) par PIVOT, il n’est plus en mesure de reconstituer en détail tout le contenu initial de la table.

Publicités

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