Microsoft SQL Server
PIVOT / UNPIVOT

Syntaxe

• SELECT <non-pivoted column> ,
  [première colonne pivotée] AS <column name> ,
  [deuxième colonne pivotée] AS <column name> ,
  ...
  [dernière colonne pivotée] AS <column name>
  DE
  ( <SELECT query that produces the data> )
  AS <alias for the source query>
  PIVOT
  (
  <aggregation function> ( <column being aggregated> )
  POUR
  [ <column that contains the values that will become column headers> ]
  IN ([première colonne pivotée], [deuxième colonne pivotée],
  ... [dernière colonne pivotée])
  ) AS <alias for the pivot table> <optional ORDER BY clause> ;

Remarques

Pivot simple - colonnes statiques

À l'aide de la table des ventes d'articles de la base de données exemple , laissez-nous calculer et afficher la quantité totale que nous avons vendue pour chaque produit.

Cela peut être facilement fait avec un group by, mais supposons que nous "tournions" notre table de résultats de manière à ce que pour chaque ID de produit nous ayons une colonne.

SELECT [100], [145]
  FROM (SELECT ItemId , Quantity
          FROM #ItemSalesTable
       ) AS pivotIntermediate
 PIVOT (   SUM(Quantity)
           FOR ItemId IN ([100], [145])
       ) AS pivotTable

Puisque nos nouvelles colonnes sont des nombres (dans la table source), nous devons mettre entre crochets []

Cela nous donnera une sortie comme

PIVOT simple et UNPIVOT (T-SQL)

Voici un exemple simple qui montre le prix moyen d'un article par jour de la semaine.

Tout d'abord, supposons que nous ayons un tableau qui conserve des enregistrements quotidiens de tous les prix des articles.

CREATE TABLE tbl_stock(item NVARCHAR(10), weekday NVARCHAR(10), price INT);

INSERT INTO tbl_stock VALUES 
('Item1', 'Mon', 110), ('Item2', 'Mon', 230), ('Item3', 'Mon', 150), 
('Item1', 'Tue', 115), ('Item2', 'Tue', 231), ('Item3', 'Tue', 162), 
('Item1', 'Wed', 110), ('Item2', 'Wed', 240), ('Item3', 'Wed', 162), 
('Item1', 'Thu', 109), ('Item2', 'Thu', 228), ('Item3', 'Thu', 145), 
('Item1', 'Fri', 120), ('Item2', 'Fri', 210), ('Item3', 'Fri', 125),
('Item1', 'Mon', 122), ('Item2', 'Mon', 225), ('Item3', 'Mon', 140),
('Item1', 'Tue', 110), ('Item2', 'Tue', 235), ('Item3', 'Tue', 154),
('Item1', 'Wed', 125), ('Item2', 'Wed', 220), ('Item3', 'Wed', 142);

La table devrait ressembler à la suivante:

+========+=========+=======+
|   item | weekday | price |
+========+=========+=======+
|  Item1 |    Mon  |   110 |
+--------+---------+-------+
|  Item2 |    Mon  |   230 |
+--------+---------+-------+
|  Item3 |    Mon  |   150 |
+--------+---------+-------+
|  Item1 |    Tue  |   115 |
+--------+---------+-------+
|  Item2 |    Tue  |   231 |
+--------+---------+-------+
|  Item3 |    Tue  |   162 |
+--------+---------+-------+
|          . . .           |
+--------+---------+-------+
|  Item2 |    Wed  |   220 |
+--------+---------+-------+
|  Item3 |    Wed  |   142 |
+--------+---------+-------+

Afin d'effectuer une agrégation consistant à trouver le prix moyen par article pour chaque jour de la semaine, nous allons utiliser l'opérateur relationnel PIVOT pour faire pivoter le weekday de la colonne de l'expression de la valeur d'une table en valeurs de ligne agrégées comme ci-dessous:

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt;

Résultat:

+--------+------+------+------+------+------+
|  item  |  Mon |  Tue |  Wed |  Thu |  Fri |
+--------+------+------+------+------+------+
|  Item1 |  116 |  112 |  117 |  109 |  120 |
|  Item2 |  227 |  233 |  230 |  228 |  210 |
|  Item3 |  145 |  158 |  152 |  145 |  125 |
+--------+------+------+------+------+------+

Enfin, pour effectuer l'opération inverse de PIVOT , nous pouvons utiliser l'opérateur relationnel UNPIVOT pour faire pivoter les colonnes en lignes comme ci-dessous:

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt
UNPIVOT ( 
    price FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) unpvt;

Résultat:

+=======+========+=========+
|  item |  price | weekday |
+=======+========+=========+
| Item1 |    116 |     Mon |
+-------+--------+---------+
| Item1 |    112 |     Tue |
+-------+--------+---------+
| Item1 |    117 |     Wed |
+-------+--------+---------+
| Item1 |    109 |     Thu |
+-------+--------+---------+
| Item1 |    120 |     Fri |
+-------+--------+---------+
| Item2 |    227 |     Mon |
+-------+--------+---------+
| Item2 |    233 |     Tue |
+-------+--------+---------+
| Item2 |    230 |     Wed |
+-------+--------+---------+
| Item2 |    228 |     Thu |
+-------+--------+---------+
| Item2 |    210 |     Fri |
+-------+--------+---------+
| Item3 |    145 |     Mon |
+-------+--------+---------+
| Item3 |    158 |     Tue |
+-------+--------+---------+
| Item3 |    152 |     Wed |
+-------+--------+---------+
| Item3 |    145 |     Thu |
+-------+--------+---------+
| Item3 |    125 |     Fri |
+-------+--------+---------+

PIVOT dynamique

Un problème avec la requête PIVOT est que vous devez spécifier toutes les valeurs dans la sélection IN si vous souhaitez les voir sous forme de colonnes. Un moyen rapide de contourner ce problème consiste à créer une sélection dynamique IN faisant de votre dynamique PIVOT .

Pour la démonstration, nous utiliserons une table Books dans la base de données d'une Bookstore . Nous supposons que la table est tout à fait normalisée et comporte les colonnes suivantes

Table: Books
-----------------------------
BookId (Primary Key Column)
Name
Language
NumberOfPages
EditionNumber
YearOfPrint
YearBoughtIntoStore
ISBN
AuthorName
Price
NumberOfUnitsSold

Le script de création de la table sera comme suit:

CREATE TABLE [dbo].[BookList](
      [BookId] [int] NOT NULL,
      [Name] [nvarchar](100) NULL,
      [Language] [nvarchar](100) NULL,
      [NumberOfPages] [int] NULL,
      [EditionNumber] [nvarchar](10) NULL,
      [YearOfPrint] [int] NULL,
      [YearBoughtIntoStore] [int] NULL,
[NumberOfBooks] [int] NULL,
[ISBN] [nvarchar](30) NULL,
      [AuthorName] [nvarchar](200) NULL,
      [Price] [money] NULL,
      [NumberOfUnitsSold] [int] NULL,
 CONSTRAINT [PK_BookList] PRIMARY KEY CLUSTERED
(
      [BookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Maintenant, si nous avons besoin d'interroger sur la base de données et de déterminer le nombre de livres en anglais, russe, allemand, hindi et latin achetés dans la librairie chaque année et de présenter notre production dans un petit format de rapport, nous pouvons utiliser la requête PIVOT

SELECT * FROM
  (
   SELECT YearBoughtIntoStore AS [Year Bought],[Language], NumberOfBooks
   FROM BookList
  ) sourceData 
 PIVOT
  (
  SUM(NumberOfBooks)
  FOR [Language] IN (English, Russian, German, Hindi, Latin)
  ) pivotrReport

Le cas particulier est lorsque nous n'avons pas une liste complète des langues, nous allons donc utiliser le SQL dynamique comme ci-dessous

DECLARE @query VARCHAR(4000)
DECLARE @languages VARCHAR(2000)
SELECT @languages =
        STUFF((SELECT DISTINCT '],['+LTRIM([Language])FROM [dbo].[BookList]
        ORDER BY '],['+LTRIM([Language]) FOR XML PATH('') ),1,2,'') + ']'
SET @query=
'SELECT * FROM
  (SELECT YearBoughtIntoStore AS [Year Bought],[Language],NumberOfBooks
   FROM BookList) sourceData
PIVOT(SUM(NumberOfBooks)FOR [Language] IN ('+ @languages +')) pivotrReport' EXECUTE(@query)