Microsoft SQL Server
PIVOT / UNPIVOT

Sintassi

• SELECT <non-pivoted column> ,
  [prima colonna imperniata] AS <column name> ,
  [seconda colonna ruotata] AS <column name> ,
  ...
  [ultima colonna imperniata] AS <column name>
  A PARTIRE DAL
  ( <SELECT query that produces the data> )
  AS <alias for the source query>
  PERNO
  (
  <aggregation function> ( <column being aggregated> )
  PER
  [ <column that contains the values that will become column headers> ]
  IN ([prima colonna imperniata], [seconda colonna imperniata],
  ... [ultima colonna imperniata])
  ) AS <alias for the pivot table> <optional ORDER BY clause> ;

Osservazioni

Pivot semplice - Colonne statiche

Utilizzando la tabella delle vendite degli articoli dal database di esempio , calcoliamo e mostriamo la quantità totale venduta di ciascun prodotto.

Questo può essere fatto facilmente con un gruppo di, ma assumiamo che dobbiamo "ruotare" la nostra tabella dei risultati in modo che per ogni ID prodotto abbiamo una colonna.

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

Poiché le nostre "nuove" colonne sono numeri (nella tabella di origine), abbiamo bisogno di parentesi quadre []

Questo ci darà un risultato simile

Semplice PIVOT & UNPIVOT (T-SQL)

Di seguito è riportato un semplice esempio che mostra il prezzo medio dell'articolo di ciascun articolo per giorno della settimana.

In primo luogo, supponiamo di avere un tavolo che tiene registri giornalieri dei prezzi di tutti gli articoli.

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 tabella dovrebbe apparire come di seguito:

+========+=========+=======+
|   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 |
+--------+---------+-------+

Per eseguire l'aggregazione che consiste nel trovare il prezzo medio per articolo per ogni giorno della settimana, useremo l'operatore relazionale PIVOT per ruotare la weekday della weekday di espressione con valori di tabella in valori di riga aggregati come di seguito:

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

Risultato:

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

Infine, per eseguire l'operazione inversa di PIVOT , possiamo utilizzare l'operatore relazionale UNPIVOT per ruotare le colonne in righe come di seguito:

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;

Risultato:

+=======+========+=========+
|  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 dinamico

Un problema con la query PIVOT è che devi specificare tutti i valori all'interno della selezione IN se vuoi vederli come colonne. Un modo rapido per aggirare questo problema è quello di creare una selezione IN dinamica che renda dinamico il tuo PIVOT .

Per la dimostrazione useremo una tabella Books nel database di un Bookstore Books . Partiamo dal presupposto che la tabella è abbastanza de-normalizzata e ha colonne seguenti

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

Lo script di creazione per la tabella sarà simile a:

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

Ora se abbiamo bisogno di interrogare il database e calcolare il numero di libri in inglese, russo, tedesco, hindi, lingue latine comprate nel bookstore ogni anno e presentare il nostro output in un piccolo formato di report, possiamo usare la query PIVOT come questa

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

Caso speciale è quando non abbiamo un elenco completo delle lingue, quindi utilizzeremo SQL dinamico come di seguito

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)