PIVOT e UNPIVOT in SQL Server

Datamaze
03.11.21 11:37 AM Comment(s)

Le operazioni di PIVOT e UNPIVOT son ben note a chiunque debba lavorare con strumenti quali Excel. In SQL Server, questi due operatori relazionali possono essere utilizzati per modificare un’espressione con valori di tabella in un’altra tabella. Essi consentono rispettivamente di trasformare valori di una tabella in valori di colonne e, viceversa, valori di colonna in valori di tabella. Vediamo come usarli.


PIVOT

Questa operazione trasforma i valori di una tabella in colonne della tabella stessa. Vediamo degli esempi.

Poniamo di avere una tabella anagrafica dei prodotti di questo tipo:

CREATE TABLE Prodotto
(
    [CodProdotto] varchar(10)
  , [Categoria]   varchar(100) 
  , [Anno]        numeric(4,0)
  , [Prezzo]      numeric(11,2)
)

In ogni record abbiamo il codice del prodotto, la sua categoria, la sua marca ed il prezzo. Poniamo di avere i seguenti dati in tabella:

Tabella pivot

Vogliamo calcolare il prezzo medio per categoria, ma avendo le categorie come intestazioni di colonna di una nuova tabella. Il risultato dovrà quindi risultare in un unico record, come segue:

Risultato tabella pivot
SELECT * FROM
(
    SELECT [Categoria], [Prezzo]
    FROM   [dbo].[Prodotti]

) T
PIVOT
(
    AVG( [Prezzo] )
    FOR [Categoria] IN
    (
        [BIKE]
      , [E-BIKE]
      , [SCOOTER]
      , [E-SCOOTER]

) AS PivotTable
;

Ogni colonna addizionale che si aggiungerà alla lista di selezione (clausola SELECT) della query che ritorna il dataset di base per il pivoting (subquery T), formerà automaticamente dei raggruppamenti nella tabella finale risultante. Per esempio:

SELECT * FROM
(
    SELECT [Categoria], [Anno], [Prezzo] -- N.B. è stato aggiunto l’anno
    FROM   [dbo].[Prodotti]

) T
PIVOT
(
    AVG( [Prezzo] )
    FOR [Categoria] IN
    (
        [BIKE]
      , [E-BIKE]
      , [SCOOTER]
      , [E-SCOOTER]

) AS PivotTable
;

 dà come risultato il seguente dataset:

PIVOT dataset risultato

UNPIVOT

Essa trasforma le colonne di una tabella in valori della tabella stessa. Vediamone degli esempi.

Poniamo di avere una tabella delle vendite d’abbigliamento di questo tipo:

CREATE TABLE Vendite
(
    [NrOrdine]    varchar(6)
  , [CodArticolo] varchar(10)
  , [QtaXXS]      int
  , [QtaXs]       int
  , [QtaS]        int
  , [QtaM]        int
  , [QtaL]        int
  , [QtaXL]       int
  , [QtaXXL]      int
)

In ogni record abbiamo il numero dell’ordine di vendita, il codice dell’articolo che vendiamo, e sette colonne ciascuna indicante la quantità di articolo venduti per ciascuna taglia, dalla XXS alla XXL. Per semplicità di esempio, prendiamo un solo record da questa tabella:

UNPIVOT risultato

Vogliamo trasformare questo singolo record in una tabella siffatta:

UNPIVOT tabella

In altre parole, vogliamo che le sette colonne divengano dei valori di tabella, generando sette record – uno per ciascuna delle sette taglie – a partire dall’unico che abbia preso in considerazione. Per far ciò, eseguiremo un’operazione di UNPIVOT, scrivendo la seguente query:

SELECT
    U.[NrOrdine]
  , U.[CodArticolo]
  , RTRIM( SUBSTRING( U.[Taglia], 4, 3 ) ) AS [Taglia]
  , U.[Qta]
FROM
    [dbo].[Vendite] V
    UNPIVOT
    (
        [Qta]
        FOR [Taglia] IN ( [QtaXXS], [QtaXS], [QtaS], [QtaM], [QtaL], [QtaXL], [QtaXXL] )
    ) U
;

NB. Senza l’operazione di SUBSTRING, il valore stringa della nuova colonna [Taglia]comprenderebbe anche il Qtainiziale. 

Questo esempio è particolarmente semplice dato che le colonne delle quantità hanno tutte lo stesso tipo di dato, ovvero int. Se avessimo invece colonne di tipo diverso, anche solo nella lunghezza (per le stringhe) o precisione e scala (per i decimali)? In questo caso, è sufficiente dapprima convertire tutte le colonne interessate dall’operazione di UNPIVOT. Risulta di estrema comodità l’utilizzo di una common table expression: vediamo col seguente esempio, dove abbiamo delle colonne di tipo stringa, che poniamo essere tutte di differente lunghezza per definizione, e che decidiamo di convertire in nvarchar(MAX).


WITH CTE_AnagCli AS
(
    SELECT
        [CodCliente]
      , CAST( [CodFiscale] AS nvarchar(MAX) ) AS [CodFiscale]
      , CAST( [PartitaIva] AS nvarchar(MAX) ) AS [PartitaIva]
      , CAST( [Indirizzo] AS nvarchar(MAX) ) AS [Indirizzo]
      , CAST( [Località] AS nvarchar(MAX) ) AS [Località]
      , CAST( [CAP] AS nvarchar(MAX) ) AS [CAP]
      , CAST( [Comune] AS nvarchar(MAX) ) AS [Comune]
      , CAST( [Provincia] AS nvarchar(MAX) ) AS [Provincia]
    FROM
        [dbo].[AnagraficaCliente]
)
SELECT
    U.[CodCliente]
  , U.[Campo] 
  , U.[Valore]
FROM
    CTE_AnagCli S
    UNPIVOT
    (
        [Valore]
        FOR [Campo] IN
        (
            [CodFiscale]
          , [PartitaIva]
          , [Indirizzo]
          , [Località]
          , [CAP]
          , [Comune]
          , [Provincia]
        )
    ) U
WHERE
    1 = 1
    AND U.[Valore] <> '' -- Rimuovo i record dove manca l’informazione
;

In questo modo, trasformeremo il primo record (dove CodFiscale e PartitaIvasono valorizzate a stringa vuota) 

UNPIVOT record

nella tabella sottostante:

UNPIVOT risultato

Conclusioni su PIVOT e UNPIVOT in SQL Server

Come abbiamo visto, lo scopo dell’operatore PIVOT è trasformare i valori univoci sulle righe di una colonna in vere e proprie colonne con aggregazione dei risultati; UNPIVOT ruota le colonne in righe, ovvero trasforma i valori di colonna in valori della tabella, posizionandoli su più righe.


Queste trasformazioni sono di estrema utilità in differenti scenari: non solo per l’analisi dei dati, ma anche in contesti di Business Intelligence quali la realizzazione di un report od una dashboard, oppure nelle fasi di trasformazione dei dati in ETL (o, più in generale, nella Data Integration).


di Thomas Tolio, pubblicato il 3 novembre 2021