Scrivere una PIVOT dinamica su SQL Server ed eseguirla con sp_executesql

Quando utilizziamo la clausola PIVOT di SQL Server dobbiamo specificare manualmente i possibili valori della colonna su cui effettuare il pivot. Ad esempio scriveremo

WITH CTE AS (
 SELECT YEAR(DataFattura) AS Anno,
    IdFornitore, 
    Importo
 FROM Fatture
 )
SELECT Anno,
 [1],
 [2]
FROM CTE
PIVOT (SUM(Importo) 
       FOR IdFornitore IN ([1],[2]) ) AS pvt;

Ma come ci comportiamo se un giorno sarà presente una riga con un nuovo fornitore? A meno di modificare manualmente la query, i dati non saranno aggiornati in automatico. Questo a meno di utilizzare dell’SQL dinamico, vediamo come nel prossimo paragrafo.

Ricorda: non copiare e incollare il codice senza aver effettuato tutti i test necessari per ogni caso specifico.

Creare dinamicamente l’elenco delle nuove colonne della Pivot

Il primo passo da effettuare è creare una variabile che calcoli dinamicamente l’elenco dei valori della colonna da pivotare, nel nostro esempio si tratta dell’IdFornitore. Per far questo dichiariamo preliminarmente una variabile @ElencoFornitori di tipo NVARCHAR(4000)  inizializzata con la stringa vuota.

DECLARE @ElencoFornitori NVARCHAR(4000) = N'';

Il secondo passo sarà valorizzare ElencoFornitori con la lista di valori, separati da una virgola e quotati. Se stiamo utilizzando una versione di SQL Server dalla 2017 in poi, possiamo utilizzare l’utilissima funzione STRING_AGG

DECLARE @ElencoFornitori NVARCHAR(4000) = N'';
WITH Fornitori AS
(SELECT DISTINCT IdFornitore
 FROM Fatture
 WHERE IdFornitore IS NOT NULL)
SELECT @ElencoFornitori = STRING_AGG(QUOTENAME(IdFornitore),',')
FROM Fornitori;
SELECT @ElencoFornitori

La query precedente restituirà un risultato di questo tipo [1],[2],[3]. 

Alcune osservazioni:

  • l’utilizzo di QUOTANAME è molto importante per prevenire alcune tipologie di attacchi di SQL Injection. Gestisce in automatico anche l’aggiunta di escape nel caso le parentesi quadre fossero presenti all’interno della colonna. Tuttavia QUOTENAME può ricevere in input fino a 128 caratteri, altrimenti restituisce NULL. Qui puoi leggere la documentazione della funzione https://docs.microsoft.com/it-it/sql/t-sql/functions/quotename-transact-sql?view=sql-server-ver16;
  • quando scriviamo codice dinamico dobbiamo sempre valutare i rischi relativi all’SQL Injection ed eventualmente aggiungere ulteriori protezioni da valutare caso per caso;
  • occorre sempre fare attenzione alla lunghezza dei dati, essa deve rispettare sia i limiti imposti dalle funzioni (come QUOTENAME) e sia la definizione delle variabili;
  • se volessimo pivotare anche i NULL, allora potremmo aggiungere una COALESCE nella prima SELECT e togliere il filtro WHERE IdFornitore IS NOT NULL. Attenzione a modificare anche la query del paragrafo successivo che esegue la Pivot.

Creare la query dinamica con la PIVOT

A questo punto possiamo comporre una variabile codice_sql che conterrà il codice da eseguire.

DECLARE @codice_sql NVARCHAR(4000) = N''; 
SELECT @codice_sql = N'WITH CTE AS (
SELECT YEAR(DataFattura) AS Anno,
IdFornitore,
Importo
FROM Fatture
)
SELECT Anno,'
+ @ElencoFornitori + 
'FROM CTE
PIVOT (SUM(Importo)
FOR IdFornitore IN (' + @ElencoFornitori + ')
) AS pvt;'
SELECT @codice_sql;

Abbiamo concatenato cinque stringhe relative a cinque porzioni di codice:

  • la prima parte della query contenente la CTE e la prima parte della SELECT
  • la variabile creata in precedenza
  • la seconda parte della query contenente l’operatore PIVOT fino all’apertura della parentesi
  • di nuovo la variabile creata in precedenza
  • l’ultima porzione di query in cui chiudiamo la parentesi e diamo un nome alla parte relativa alla Pivot.
Video Corso T-SQL avanzato

Eseguire la query dinamica

A questo punto possiamo eseguire il codice tramite l’istruzione

EXEC sp_executesql @stmt = @codice_sql;

e otterremo lo stesso risultato di partenza, ma tramite del codice dinamico che aggiornerà in automatico l’elenco delle colonne in output. Ovviamente dobbiamo eseguire l’intero codice in un unico statement altrimenti otterremo messaggi di errore relativi al fatto che le variabili non esistono.

Conclusioni sulle Pivot Dinamiche con T-SQL

In questo articolo abbiamo visto come creare una query dinamica con il linguaggio T-SQL per eseguire il PIVOT dei dati senza conoscere preventivamente l’elenco dei valori. Gli ingredienti principali del nostro codice sono le funzioni STRING_AGG e QUOTENAME per generare l’elenco di colonne e la stored procedure di sistema sp_executesql per generare il codice creato dinamicamente.

Se vuoi formarti come un Data Analyst a 360 gradi, guarda la pagina del mio video corso completo che copre tutti gli strumenti più richiesti nel mondo del lavoro come MongoDB, Python, SQL e Power BI https://www.yimp.it/corso-data-analysis/

Altri articoli del blog che potrebbero interessarti:

Torna in alto
Torna su