Convertire righe in colonne con CASE WHEN e PIVOT del linguaggio SQL

In questo articolo vediamo come elaborare con l’SQL i dati di una tabella con una struttura chiave-valore in cui per un solo “elemento” abbiamo tante righe in due colonne generiche, per ottenere una classica struttura relazionale in cui le informazioni sono riportate in una sola riga con tante colonne, ognuna con il relativo nome specifico.

Spieghiamo meglio il problema con un esempio, partiamo da una struttura dati come questa dove la chiave primaria è data dalla coppia di colonne IdFattura – Chiave

e vogliamo arrivare a questo output

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

Soluzione con CASE WHEN

Possiamo affrontare il problema combinando una Group by e più Case When. Scriveremo

SELECT 
  IdFattura,
  MAX(CASE WHEN Chiave = 'Importo' 
       THEN Valore
       ELSE NULL
      END) AS Importo,
  MAX(CASE WHEN Chiave = 'Iva' 
       THEN Valore
       ELSE NULL
      END) AS Iva,
  MAX(CASE WHEN Chiave = 'IdCliente' 
       THEN Valore
       ELSE NULL
      END) AS IdCliente
FROM Tabella
GROUP BY
  IdFattura;

La scelta della funzione di aggregazione MAX (o equivalente MIN) non influenza il risultato in quanto abbiamo ipotizzato che la coppia IdFattura – Chiave sia primary key della tabella di partenza. Di conseguenza per ogni IdFattura esiste una sola riga con una determinata chiave. Risulta comunque importante osservare che, a differenza della SUM, MAX e MIN non genereranno errori per valori di tipo stringa.

Soluzione con PIVOT

Su SQL Server possiamo utilizzare il costrutto Pivot:

SELECT 
 IdFattura,
 Importo,
 Iva,
 IdCliente
FROM Tabella AS T
PIVOT (MAX(Valore)
        FOR Chiave IN ([Importo],[Iva],[IdCliente]) 
       ) AS pvt

Osserviamo che se nella tabella fossero presenti anche altre colonne, allora nella query dovrei sostituire Tabella con una CTE in cui selezioniamo solo le tre colonne di interesse. In questo articolo ho descritto come dinamizzare la query con la Pivot affinché si aggiorni in automatico in presenza di nuove chiavi.

Conversione dei tipi

Ci aspettiamo che nei dati di input la colonna Valore sia di tipo varchar o nvarchar. Probabilmente vorremmo effettuare anche una conversione dei dati di output. Per far questo potremmo utilizzare le funzioni CONVERT o TRY_CONVERT, con un’importante differenza in caso di errori di conversione:

• la query con la CONVERT genererà un errore;
• nella query con la TRY_CONVERT non avrò errori di esecuzione e per le eventuali conversioni non riuscite comparirà un NULL.

Dobbiamo porre particolare attenzione alla presenza di particolari separatori dei decimali e delle migliaia e al formato delle date. Per il primo caso possiamo utilizzare due funzioni REPLACE annidate. Ad esempio potrei dover scrivere una query per:

1. eliminare il carattere “.” come separatore delle migliaia;
2. sostituire la virgola “,” con il punto “.” come separatore dei decimali.

La query conterrà allora questa espressione:

REPLACE(
    REPLACE(Valore,'.',''),
    ',',
    '.')

Per i formati di date, possiamo utilizzare il terzo argomento delle funzioni CONVERT/TRY_CONVERT come indicato nella documentazione ufficiale https://docs.microsoft.com/it-it/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

Conclusioni

Il problema di convertire righe in colonne è abbastanza frequente quando importiamo dei dati provenienti da database non relazionali. In questo articolo abbiamo visto due possibili soluzioni con l’espressione CASE WHEN e l’operatore PIVOT. Ecco una lista di articoli utili che puoi continuare a leggere sul mio blog

• Normalizziamo un database con l’SQL
• Scriviamo una Pivot SQL dinamica 
• Analizziamo il problema del Parameter Sniffing