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ì:

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:

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
...	…	…	…
…	…	…	…

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 una sola riga anni diventano le intestazioni di colonna PIVOT

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
• La seconda SELECT FROM
• Attraverso l'operatore PIVOT SUM(Value)
• Dopo la clausola FOR Year
• Dopo la parola chiave IN FOR

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 compatta facile più facile da ottimizzare

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 CASE

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 Seller Sells PIVOT

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 permette di scrivere query più compatte UNPIVOT più efficiente UNION

È importante notare che, sebbene UNPIVOT PIVOT UNPIVOT non è propriamente l'esatto opposto PIVOT PIVOT UNPIVOT NULL UNPIVOT PIVOT

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.