PIVOT e UNPIVOT per principianti

Prima di cominciare

Ovviamente, se siete arrivati ad interessarvi di PIVOT, tanto principianti non lo siete. Non è mia intenzione offendere nessuno.
Nell'SQL "moderno" è presente un comando che, in certi contesti, può risolvere problematiche legate alla differenza esistente fra come i dati sono strutturati all'interno di un database e la presentazione che ne dev'essere fatta all'utente.
Il contesto e l'utilizzo di PIVOT / UNPIVOT, per risolvere le problematiche che ho accennato, sono di più facile comprensione tramite un esempio, che racchiude un caso "tipico".
Nota: Negli esempi di codice ho usato la sintassi T-SQL, propria di SqlServer; nelle altre versioni di SQL non penso ci siano grosse differenze.

Il fratello maggiore: PIVOT

Iniziamo col considerare una classica tabella contenente i dati di fatturazione per un'Azienda. In questo esempio, ovviamente, i dati sono ridotti al minimo indispensabile allo scopo di questo tutorial.

WITH
RecordsetOriginale AS (
SELECT Anno, Agente, ImportoFatturato
FROM TabFatturato
WHERE Anno IN (2015,2016)
)
SELECT * FROM RecordsetOriginale

Anno	Agente	ImportoFatturato
2015	Agente AAA	6320.58
2015	Agente BBB	5689.35
2015	Agente CCC	157896.25
2016	Agente AAA	8541.21
2016	Agente CCC	7563.26
2016	Agente DDD	10256.36
...	...	...

I dati sono organizzati in modo classico: il fatturato è diviso per Agente e per Anno.
Ora, partendo da questa struttura, è possibile che la sezione Contabilità della suddetta Azienda, vi chieda di visualizzare questi dati in modo differente da come sono organizzati. La richiesta potrebbe facilmente essere: si possono mostrare i dati del Fatturato incolonnati per Anni di fatturazione e gli Agenti sulle righe, in modo da visualizzare l'andamento di Fatturato per Agente duranti gli Anni? In pratica si vorrebbe una visualizzazione dati come nella seguente tabella:

Agente	2015 (ImportoFatturato)	2016 (ImportoFatturato)
Agente AAA	6320.58	8541.21
Agente BBB	5689.35	---
Agente CCC	157896.25	7563.26
Agente DDD	---	10256.36
...	...	...

Questo genere di richiesta può essere affrontata in maniera abbastanza semplice con dei comandi già presenti in SQL. L'alternativa sarebbe elaborare noi stessi, tramite codice, i dati prima di presentarli come voluto. Ovviamente l'alternativa è la strada sbagliata: perché reinventare l'acqua calda? I database relazionali moderni prevedono i comandi che fanno già questo genere di operazioni: PIVOT e UNPIVOT. Partiamo dal primo.
PIVOT permette di utilizzare i valori di un campo in modo che diventino colonne del dataset risultante e contemporaneamente traslare i valori di un altro campo in modo che diventino i valori delle nuove colonne (è più difficile spiegarlo a parole che vederlo in pratica). È quello che è accaduto nella tabella di esempio precedente. I valori del campo "Anno" (2015, 2016) sono diventati nuove colonne. I valori del campo "ImportoFatturato" sono diventati i valori delle nuove colonne. Il campo "Agente" invece rimane al suo posto, perché escluso dal PIVOT.
Vediamo il codice SQL che permette di ottenere questo:

WITH
RecordsetOriginale AS (
SELECT Anno, Agente, ImportoFatturato FROM TabFatturato
WHERE Anno IN (2015,2016)
),
DSBase_PIVOTATO AS (
SELECT * FROM RecordsetOriginale
PIVOT (MAX(ImportoFatturato) FOR Anno IN([2015],[2016])) NomeObbligatorioPerIlPivot
)
SELECT * FROM DSBase_PIVOTATO

Spiegazioni nel merito:

Con WITH si dichiarano due sub-query: la prima genera un dataset contenente i dati "grezzi" estrapolati dalla tabella "madre", la seconda applica il PIVOT ai dati della prima.
PIVOT necessita di una funzione aggregatrice per i dati contenuti nel campo i cui valori verranno traslati, "ImportoFatturato" nel nostro caso. La funzione MAX() mi permette di avere tale funzione aggregatrice senza stravolgere il valore del dato stesso.
FOR introduce il campo i cui valori diverranno nuove colonne, "Anno" nel nostro esempio.
Il successivo IN permette di specificare quali valori sono da utilizzare per creare le nuove colonne. È buona norma delimitare questi valori con la sintassi utilizzata per isolare i nomi degli oggetti, [ ] in T-SQL.
"NomeObbligatorioPerIlPivot" è necessario per rispettare la sintassi di PIVOT, ma nel nostro caso ha poca importanza.
Finito con le sub-query, utilizzo un semplice comando SQL per estrapolare i dati "pivotati".

Il recordset finale risultante sarà:

Agente	2015	2016
Agente AAA	6320.58	8541.21
Agente BBB	5689.35	NULL
Agente CCC	157896.25	7563.26
Agente DDD	NULL	10256.36
...	...	...

Un'ultima annotazione. È interessante notare che PIVOT si prende la briga, durante l'operazione di traslazione, di verificare che esista un valore da traslare e, in caso contrario, di restituire NULL. Questo permette di avere in lista anche Agenti che non hanno fatturato per uno degli anni presi in considerazione. Nel nostro esempio mi riferisco all'Agente BBB o DDD.

Il fratello minore: UNPIVOT

Non è per denigrarlo, è solo che si usa di meno. UNPIVOT, per farla semplice, fa il contrario di PIVOT. In realtà non è proprio così, ma per questa guida possiamo semplificarla in questo modo: UNPIVOT prende i dati contenuti in più colonne e li raggruppa in una nuova colonna.
Come per PIVOT, anche per UNPIVOT è più facile capirne il funzionamento tramite un esempio. Partendo da quello precedente, utilizzato con PIVOT, approdiamo ad un nuovo scenario: come fare se i valori da traslare appartengono a più colonne?

WITH
RecordsetOriginale AS (
SELECT Anno, Agente, ImportoFatturato, ImportoMargine FROM TabFatturato
WHERE Anno IN (2015,2016)
)
SELECT * FROM RecordsetOriginale

Che produrrà questo recordset base:

Anno	Agente	ImportoFatturato	ImportoMargine
2015	Agente AAA	6320.58	1325.66
2015	Agente BBB	5689.35	986.24
2015	Agente CCC	157896.25	3844.82
2016	Agente AAA	8541.21	2745.20
2016	Agente CCC	7563.26	2056.33
2016	Agente DDD	10256.36	2657.25
...	...	...	...

Nella tabella precedente, oltre alla colonna "ImportoFatturato", è ora presente anche la colonna "ImportoMargine", che contiene il margine operativo realizzato dall'Agente. È solitamente un dato importante, quindi è ragionevole che vi venga richiesta una presentazione dei dati come nella tabella seguente:

Agente	2015 (ImportoFatturato)	2016 (ImportoFatturato)	2015 (ImportoMargine)	2016 (ImportoMargine)
Agente AAA	6320.58	8541.21	1325.66	2745.20
Agente BBB	5689.35	---	986.24	---
Agente CCC	157896.25	7563.26	3844.82	2056.33
Agente DDD	---	10256.36	---	2657.25
...	...	...	...	...

Questo risultato non si può ottenere con un PIVOT semplice, in quanto la traslazione è possibile solo per un singolo campo. Per ovviare a questo impedimento, si usa una tecnica che consiste nel combinare i comandi UNPIVOT e PIVOT in maniera seriale: UNPIVOT ridurrà le colonne i cui valori sono da traslare ad una sola colonna, PIVOT traslerà i valori di quest'ultima per ottenere il risultato finale voluto.
Iniziamo con l'applicare l'UNPIVOT al dataset base:

WITH
RecordsetOriginale AS (
SELECT Anno, Agente, ImportoFatturato, ImportoMargine FROM TabFatturato
WHERE Anno IN (2015,2016)
),
DSBase_UNPIVOT AS (
SELECT * FROM (
SELECT Agente,
CASE
WHEN ColonnaAnalizzata = 'ImportoFatturato' THEN 'F'
WHEN ColonnaAnalizzata = 'ImportoMargine' THEN 'M'
END + CAST(Anno AS varchar(4)) NomeColonnaUNPIVOT,
value
FROM RecordsetOriginale
UNPIVOT (
value
FOR ColonnaAnalizzata IN ([ImportoFatturato],[ImportoMargine])
) NomeObbligatorioUnPivot
) UnPivotResulSet
)

Che produrrà il seguente poco appetibile risultato:

Agente	NomeColonnaUNPIVOT	value
Agente AAA	F2015	6320.58
Agente AAA	M2015	1325.66
Agente BBB	F2015	5689.35
Agente BBB	M2015	986.24
Agente CCC	F2015	157896.25
Agente CCC	M2015	3844.82
Agente AAA	F2016	8541.21
Agente AAA	M2016	2745.20
Agente CCC	F2016	7563.26
Agente CCC	M2016	2056.33
Agente DDD	F2016	10256.36
Agente DDD	M2016	2657.25
...	...	...

Guardando la tabella risultato, è abbastanza evidente capire cosa ha fatto l'UNPIVOT:

Per ogni record del dataset base sono stati creati due record, uno per il Fatturato e l'altro per il Margine.
È stata creata una nuova colonna "NomeColonnaUNPIVOT", nella quale sono confluiti i nomi delle colonne specificate nel FOR...IN ("ImportoFatturato" e "ImportoMargine") opportunamente ridefiniti in base alla sorgente e all'Anno ("F2015", "M2015", etc.).
Il valore originale delle colonne specificate nel FOR...IN è stato mantenuto tramite l'utilizzo della keyword "value".

NOTA IMPORTANTE: I campi che confluiscono nella colonna "value" (nel nostro caso "ImportoFatturato" e "ImportoMargine") devono essere dello stesso tipo! Se non lo fossero, è necessario operare un opportuno casting dei dati prima di poter passare i dati all'UNPIVOT.

A questo punto possiamo applicare un PIVOT al dataset risultante dall'UNPIVOT, e traslare gli importi in nuove colonne che avranno il nome preso dai valori della colonna "NomeColonnaUNPIVOT" (ovvero "F2015", "M2015", etc.). Come già detto precedentemente, un esempio visivo chiarirà meglio il concetto:

WITH
RecordsetOriginale AS (
SELECT Anno, Agente, ImportoFatturato, ImportoMargine FROM TabFatturato
WHERE Anno IN (2015,2016)
),
DsBase_UNPIVOT_PIVOT AS (
SELECT * FROM (
SELECT Agente,
CASE
WHEN ColonnaAnalizzata = 'ImportoFatturato' THEN 'F'
WHEN ColonnaAnalizzata = 'ImportoMargine' THEN 'M'
END + CAST(Anno AS varchar(4)) NomeColonnaUNPIVOT,
value
FROM RecordsetOriginale
UNPIVOT (
value
FOR ColonnaAnalizzata IN ([ImportoFatturato],[ImportoMargine])
) NomeObbligatorioUnPivot
) UnPivotResulSet
PIVOT (MAX(value) FOR NomeColonnaUNPIVOT IN([F2015],[M2015],[F2016],[M2016])) NomeObbligatorioPivot
)
SELECT * FROM DsBase_UNPIVOT_PIVOT

Che produrrà un meraviglioso:

Agente	F2015	M2015	F2016	M2016
Agente AAA	6320.58	1325.66	8541.21	2745.20
Agente BBB	5689.35	986.24	NULL	NULL
Agente CCC	157896.25	3844.82	7563.26	2056.33
Agente DDD	NULL	NULL	10256.36	2657.25
...	...	...	...	...

Che è la presentazione dei dati che volevamo.
Concludo con un'ultima precisazione e un'ultimissima raccomandazione: questo è un tutorial estremamente semplificato, con l'unico scopo di spiegare uno dei modi da cui trarre giovamento nell'utilizzare i comandi PIVOT ed UNPIVOT; se volete approfondire, e lo consiglio, fate riferimento alla documentazione del database che state utilizzando.

Esclusione di Responsabilità

Tutto il software scaricato da (e/o il codice fornito da) questo sito è fornito "COSÌ COM'È" senza alcuna garanzia, sia espressa che implicita. L'autore non potrà essere ritenuto responsabile per eventuali danni derivanti dall'utilizzo del software e/o del codice.

Per qualsiasi chiarimento e/o suggerimento, potete contattarmi all'indirizzo e-mail: info@gmas.it

Testi, immagini, filmati, software sono opera mia, salvo dove diversamente specificato.