SQL: Pivot e Unpivot

Gli operazioni relazionali PIVOT e UNPIVOT, introdotti con SQL Server 2005, permettono di "ruotare" i risultati di una query SQL, utilizzando le colonne al posto delle righe e viceversa. Detto in parole semplici, lo scopo dell'operatore PIVOT (letteralmente "perno") è quello di trasformare i valori univoci sulle righe in colonne con aggregazione dei dati risultanti. Si tratta di un concetto che dovrebbe risultare familiare a chi ha già utilizzato le tabelle PIVOT di Excel o le query a campi incrociati di Access. L'operatore UNPIVOT, invece, esegue l'operazione contraria rispetto a PIVOT, ruotando le colonne in valori di riga.

Va detto che gli stessi risultati ottenibili con PIVOT, prima di SQL Server 2005, si potevano raggiungere con una serie di istruzioni SELECT ... CASE e, analogamente, la funzione UNPIVOT poteva essere simulata per mezzo di diverse query con UNION. Tuttavia, come vedremo tra poco, l'introduzione di questi operatori ha permesso di semplificare la scrittura delle query e rendere il codice più leggibile.

Utilizzo dell'operatore PIVOT

Partiamo subito con un esempio di utilizzo dell'operatore PIVOT. Innanzi tutto, creiamo un database di nome PivotTest, utilizzando lo script Create.sql. Otterremo uno schema con la tabella Sells, al cui interno saranno memorizzati i totali di vendita, divisi per anni, dei vari impiegati:

Ora inseriamo alcuni dati con lo script Insert.sql, ottenendo una tabella che si presenta così:

SellID	Year	Seller	Value
1	2009	Marco	143,3
2	2009	Andrea	129
3	2009	Carlo	90,5
4	2009	Roberto	111
5	2009	Luigi	120,1
6	2008	Marco	130,7
7	2008	Andrea	170,9
8	2008	Carlo	167
...	…	…	…
…	…	…	…

Ad esempio, vediamo che Andrea nel 2009 ha venduto 129, mentre nel 2008 aveva raggiunto quota 170,9. La tabella contiene dati di questo tipo fino al 2006. Avendo le informazioni così memorizzate, è molto semplice ottenere il totale delle vendite anno per anno; è infatti sufficiente una query con raggruppamento sull'anno e una funzione di aggregazione:

SELECT [Year], SUM(Value) As TotalSold
FROM Sells
GROUP BY [Year];

Il cui risultato, come è facile intuire, è il seguente:

Year	TotalSold
2009	452,9
2008	522,2
2007	720,2
2006	593,9

Quello che abbiamo è una riga per ogni anno. Supponiamo però di voler ottenere un risultato "ruotato", ovvero una sola riga, in cui gli anni diventano le intestazioni di colonna: è in questo caso che ci viene in aiuto l'operatore PIVOT. Grazie ad esso, infatti, per raggiungere il risultato voluto è sufficiente scrivere la seguente query:

SELECT [2006], [2007], [2008], [2009]
FROM
(SELECT [Year], Value
FROM Sells) AS tmp
PIVOT
(
SUM(Value)
FOR [Year] IN ([2006], [2007], [2008], [2009])
) AS PivotTable;

In questa interrogazione possiamo identificare alcuni aspetti importanti:

• La prima clausola SELECT specifica le intestazioni di colonna (nel nostro caso gli anni)
• La seconda SELECT, all'interno della FROM, indica quali sono i dati da estrarre
• Attraverso l'operatore PIVOT specifichiamo la funzione di aggregazione che vogliamo utilizzare (SUM(Value))
• Dopo la clausola FOR indichiamo il nome della colonna contenente i valori che diventeranno intestazioni di colonna (Year)
• Dopo la parola chiave IN elenchiamo i valori dei campi che compariranno nelle intestazioni di colonna (una sorta di filtro sui valori della colonna specificata dopo FOR, ovvero gli anni che ci interessano nel nostro esempio).

Il risultato che si ottiene è dunque il seguente:

2006	2007	2008	2009
452,9	522,2	720,2	593,9

Se qualcuno ha provato ad ottenere un output di questo tipo con SQL Server 2000 o versione precedente, si renderà immediatamente conto di quanto la sintassi basata su PIVOT sia più compatta, di facile comprensione e, cosa molto importate, più facile da ottimizzare per il database engine.

Ora invece vogliamo ottenere un report di una riga che mostri il totale delle vendite di ogni persona, ovvero:

Marco	Andrea	Carlo	Roberto	Luigi
522	539,1	491,9	231,6	504,6

Come accennato in precedenza, se non avessimo a disposizione l'operatore PIVOT, dovremmo scrivere una query che utilizza diversi predicati CASE. Vediamola:

SELECT SUM(CASE Seller WHEN 'Marco' THEN Value ELSE 0 END) AS Marco,
SUM(CASE Seller WHEN 'Andrea' THEN Value ELSE 0 END) AS Andrea,
SUM(CASE Seller WHEN 'Carlo' THEN Value ELSE 0 END) AS Carlo,
SUM(CASE Seller WHEN 'Roberto' THEN Value ELSE 0 END) AS Roberto,
SUM(CASE Seller WHEN 'Luigi' THEN Value ELSE 0 END) AS Luigi
FROM Sells

In pratica, quello che stiamo facendo è calcolare la somma sulla colonna Value condizionata al valore di Seller, per tutti i venditori della tabella Sells. La stessa query, con l'operatore PIVOT, si trasforma così:

SELECT Marco, Andrea, Carlo, Roberto, Luigi
FROM
(SELECT Seller, Value
FROM Sells) AS tmp
PIVOT
(
SUM(Value)
FOR Seller IN (Marco, Andrea, Carlo, Roberto, Luigi)
) AS PivotTable

Ovviamente, il risultato che si ottiene è il medesimo, ma la seconda query ha i vantaggi che abbiamo illustrato in precedenza.

Utilizzo dell'operatore UNPIVOT

L'operatore UNPIVOT esegue l'operazione contraria rispetto a PIVOT, ruotando le colonne in righe. Supponiamo di memorizzare in una tabella temporanea (#temp) il risultato dell'interrogazione tramite PIVOT dell'esempio precedente:

SELECT Marco, Andrea, Carlo, Roberto, Luigi
INTO #temp
FROM
(SELECT Seller, Value
FROM Sells) AS tmp
PIVOT
(
SUM(Value)
FOR Seller IN (Marco, Andrea, Carlo, Roberto, Luigi)
) AS PivotTable
GO
SELECT * FROM #temp
GO

Il nostro obiettivo è partire da #temp per ottenere un output con una riga per ogni venditore (anziché un'unica riga). Senza UNPIVOT, per raggiungere il nostro scopo dobbiamo ricorrere ad una serie di UNION ALL:

SELECT 'Marco' AS Seller, Marco AS Value
FROM #temp
UNION ALL
SELECT 'Andrea' AS Seller, Andrea AS Value
FROM #temp
UNION ALL
SELECT 'Carlo' AS Seller, Carlo AS Value
FROM #temp
UNION ALL
SELECT 'Roberto' AS Seller, Roberto AS Value
FROM #temp
UNION ALL
SELECT 'Luigi' AS Seller, Luigi AS Value
FROM #temp
Con l'operatore UNPIVOT, invece, la query diventa semplicemente:
SELECT Seller, Value
FROM
(SELECT Marco, Andrea, Carlo, Roberto, Luigi
FROM #temp) AS tmp
UNPIVOT
(Value FOR Seller IN
(Marco, Andrea, Carlo, Roberto, Luigi)
) AS UnpivotTable

In entrambi i casi, il risultato che si ottiene è il seguente:

Seller	Value
Marco	522
Andrea	539,1
Carlo	491,9
Roberto	231,6
Luigi	504,6

Confrontando le due interrogazioni, la prima cosa che salta all'occhio è che, ancor più che nel caso di PIVOT, UNPIVOT permette di scrivere query più compatte. Inoltre, l'utilizzo di UNPIVOT è nettamente più efficiente rispetto all'esecuzione di numerose UNION in cascata.

È importante notare che, sebbene UNPIVOT esegua l'operazione "inversa" rispetto a PIVOT, UNPIVOT non è propriamente l'esatto opposto di PIVOT: PIVOT effettua un'aggregazione e, pertanto, può unire righe diverse in una riga singola nell'output. Di conseguenza, l'operatore UNPIVOT non è in grado di riprodurre il risultato della tabella originale, perché alcune righe possono essere state raggruppate. Inoltre, i valori NULL nell'input di UNPIVOT vengono esclusi dall'output, mentre è possibile che tali valori fossero presenti nell'input dell'operazione PIVOT iniziale.

Tutti gli esempi illustrati in questo articolo sono disponibili per il download.

Conclusioni su SQL Pivot e Unpivot

In questo articolo abbiamo presentato gli operatori PIVOT e UNPIVOT, introdotti da SQL Server 2005 per trasformare i dati di una tabella ruotando i valori di riga in colonne (con calcolo di funzioni di aggregazione) e viceversa. Si tratta di operatori molto utili per la realizzazione di report e riepiloghi dei dati, ad esempio in un contesto di data-warehouse. Per approfondire l'argomento, come sempre si consiglia di partire dalla documentazione ufficiale di MSDN.