SQL Pivot Righe in Colonne: Una Guida Completa

Cosa Significa Trasformare le Righe in Colonne in SQL?

Il pivoting in SQL si riferisce alla trasformazione dei dati da un formato basato sulle righe a un formato basato sulle colonne. Questa trasformazione è utile per la creazione di report e l’analisi dei dati, consentendo una visualizzazione dei dati più strutturata e compatta. Il passaggio da righe a colonne consente anche agli utenti di analizzare e riassumere i dati in modo da evidenziare più chiaramente le principali informazioni.

Consideriamo il seguente esempio: ho una tabella con transazioni di vendita giornaliere, e ogni riga registra la data, il nome del prodotto e l’importo delle vendite.

Attraverso il pivoting di questa tabella, posso ristrutturarla per mostrare ciascun prodotto come una colonna, con i dati delle vendite per ciascuna data sotto la rispettiva colonna. Si noti inoltre che avviene un’aggregazione.

Tradizionalmente, le operazioni di pivot richiedevano complesse query SQL con aggregazioni condizionali. Nel tempo, le implementazioni di SQL sono evolute, con molti database moderni che includono ora gli operatori PIVOT e UNPIVOT per consentire trasformazioni più efficienti e semplici.

Comprensione della trasformazione delle righe in colonne con SQL Pivot

L’operazione di pivot SQL trasforma i dati trasformando i valori delle righe in colonne. Di seguito la sintassi e la struttura di base del pivot SQL con le seguenti parti:

• SELECT: La dichiarazione SELECT fa riferimento alle colonne da restituire nella tabella pivot SQL.

• Sottoquery: La sottoquery contiene la fonte dati o la tabella da includere nella tabella pivot SQL.

• PIVOT: L’operatore PIVOT contiene le aggregazioni e i filtri da applicare nella tabella pivot.

-- Selezionare colonne statiche e colonne pivot
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- Sottointerrogazione che definisce i dati di origine per il pivot
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- Funzione di aggregazione applicata alla colonna dei valori, creando nuove colonne
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

Esaminiamo il seguente esempio passo dopo passo per dimostrare come trasformare righe in colonne in SQL. Considera la tabella SalesData di seguito.

Esempio di tabella da trasformare usando l’operatore SQL PIVOT. Immagine dell’Autore.

Voglio pivotare questi dati per confrontare le vendite giornaliere di ciascun prodotto. Inizierò selezionando la sottoquery che strutturerà l’operatore PIVOT.

-- Sottoquery che definisce i dati di origine per il pivot
SELECT Date, Product, Sales
FROM SalesData;

Ora, userò l’operatore PIVOT per convertire i valori di Prodotto in colonne e aggregare le Vendite usando l’operatore SUM.

-- Seleziona Data e colonne pivotate per ciascun prodotto
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Sottoquery per recuperare le colonne Data, Prodotto e Vendite
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggrega le Vendite per Prodotto, pivotando i valori del prodotto nelle colonne
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Output di esempio della trasformazione usando il pivot delle righe in colonne SQL. Immagine dell’Autore.

Anche se la tecnica del pivot dei dati semplifica il riepilogo dei dati, presenta potenziali problemi. Di seguito sono riportate le sfide potenziali con il pivot SQL e come affrontarle.

• Nomi di Colonne Dinamici: Quando i valori da pivotare (ad esempio, tipi di prodotto) sono sconosciuti, inserire i nomi delle colonne manualmente non funzionerà. Alcuni database, come SQL Server, supportano SQL dinamico con stored procedure per evitare questo problema, mentre altri richiedono di gestire questo problema a livello dell’applicazione.

• Gestione dei valori NULL: Quando non ci sono dati per una colonna specifica pivotata, il risultato potrebbe includere NULL. Puoi usare COALESCE per sostituire i valori NULL con zero o un altro segnaposto.

• Compatibilità tra Database: Non tutti i database supportano direttamente l’operatore PIVOT. È possibile ottenere risultati simili con istruzioni CASE e aggregazione condizionale se il tuo dialetto SQL non lo supporta.

Trasformazione delle Righe in Colonne con SQL Pivot: Esempi e Casi d’Uso

Sono utilizzati metodi diversi per ruotare i dati in SQL, a seconda del database utilizzato o di altri requisiti. Mentre l’operatore PIVOT è comunemente utilizzato in SQL Server, altre tecniche, come le istruzioni CASE, consentono trasformazioni simili del database senza il supporto diretto di PIVOT. Coprirò i due metodi comuni di rotazione dei dati in SQL e parlerò dei pro e contro.

Utilizzo dell’operatore PIVOT

L’operatore PIVOT, disponibile in SQL Server, fornisce un modo diretto per ruotare le righe in colonne specificando una funzione di aggregazione e definendo le colonne da ruotare.

Considera la seguente tabella chiamata sales_data.

Esempio tabella Ordini da trasformare utilizzando l’operatore PIVOT. Immagine dell’autore.

Utilizzerò l’operatore PIVOT per aggregare i dati in modo che il totale del fatturato_vendite di ogni anno sia mostrato in colonne.

-- Utilizzare PIVOT per aggregare il fatturato vendite per anno
SELECT *
FROM (
    -- Selezionare le colonne rilevanti dalla tabella di origine
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- Aggregare il fatturato vendite per ogni anno
    SUM(sales_revenue)
    -- Creare colonne per ogni anno
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

Trasformazione di output di esempio utilizzando SQL PIVOT. Immagine dell’Autore.

L’utilizzo dell’operatore PIVOT ha i seguenti vantaggi e limitazioni:

• Vantaggi: Il metodo è efficiente quando le colonne sono correttamente indicizzate. Ha inoltre una sintassi semplice e più leggibile.

• Limitazioni: Non tutti i database supportano l’operatore PIVOT. Richiede di specificare le colonne in anticipo e il pivoting dinamico comporta una complessità aggiuntiva.

Pivot manuale con istruzioni CASE

Puoi anche utilizzare le istruzioni CASE per eseguire il pivot manuale dei dati nei database che non supportano gli operatori PIVOT, come MySQL e PostgreSQL. Questo approccio utilizza l’aggregazione condizionale valutando ogni riga e assegnando condizionalmente valori a nuove colonne in base a criteri specifici.

Ad esempio, possiamo eseguire manualmente il pivot dei dati nella stessa tabella sales_data con istruzioni CASE.

-- Aggrega il fatturato delle vendite per anno utilizzando le istruzioni CASE
SELECT 
    -- Calcola il fatturato totale delle vendite per ciascun anno
    SUM(CASE WHEN sale_year = 2020 THEN sales_revenue ELSE 0 END) AS sales_2020,
    SUM(CASE WHEN sale_year = 2021 THEN sales_revenue ELSE 0 END) AS sales_2021,
    SUM(CASE WHEN sale_year = 2022 THEN sales_revenue ELSE 0 END) AS sales_2022,
    SUM(CASE WHEN sale_year = 2023 THEN sales_revenue ELSE 0 END) AS sales_2023
FROM 
    sales_data;

Trasformazione dell’output di esempio utilizzando l’istruzione SQL CASE. Immagine dell’Autore.

L’utilizzo dell’istruzione CASE per la trasformazione presenta i seguenti vantaggi e limitazioni:

• Vantaggi: Il metodo funziona su tutti i database SQL ed è flessibile per generare dinamicamente nuove colonne, anche quando i nomi dei prodotti sono sconosciuti o cambiano frequentemente.

• Limitazioni: Le query possono diventare complesse e lunghe se ci sono molte colonne da pivotare. A causa dei molteplici controlli condizionali, il metodo è leggermente più lento dell’operatore PIVOT.

Considerazioni sulle prestazioni durante il passaggio da righe a colonne

Il passaggio da righe a colonne in SQL può avere implicazioni sulle prestazioni, specialmente quando si lavora con grandi set di dati. Ecco alcuni consigli e best practice per aiutarti a scrivere query di pivot efficienti, ottimizzare le loro prestazioni e evitare errori comuni.

Best practice

I seguenti sono le migliori pratiche per ottimizzare le tue query e migliorare le prestazioni.

• Strategie di indicizzazione: È cruciale indicizzare correttamente le query pivot per ottimizzarle, consentendo a SQL di recuperare e elaborare i dati più velocemente. Indicizza sempre le colonne utilizzate frequentemente nella clausola WHERE o le colonne su cui stai facendo raggruppamenti per ridurre i tempi di scansione.

• Evitare pivot annidati: Impilare più operazioni di pivot in una sola query può essere difficile da leggere e più lento da eseguire. Semplifica suddividendo la query in parti o utilizzando una tabella temporanea.

• Limitare colonne e righe nel pivot: Solo le colonne pivot sono necessarie per l’analisi poiché il pivot di molte colonne può richiedere molte risorse e creare tabelle grandi.

Evitare errori comuni

Gli errori comuni che potresti incontrare nelle query pivot e come evitarli.

• Scansioni Complete Non Necessarie: Le query pivot possono scatenare scansioni complete della tabella, specialmente se non sono disponibili indici pertinenti. Evita le scansioni complete della tabella indicizzando le colonne chiave e filtrando i dati prima di applicare il pivot.

• Utilizzo di SQL dinamico per pivot frequenti: L’uso di SQL dinamico può rallentare le prestazioni a causa della ricompilazione della query. Per evitare questo problema, memorizzare nella cache o limitare i pivot dinamici a scenari specifici e considerare la gestione delle colonne dinamiche nel livello dell’applicazione quando possibile.

• Aggregazione su grandi set di dati senza prefiltraggio: Le funzioni di aggregazione come SUM o COUNT su grandi set di dati possono rallentare le prestazioni del database. Invece di pivotare l’intero set di dati, filtrare prima i dati utilizzando una clausola WHERE.

• Valori NULL nelle colonne pivot: Le operazioni di pivot spesso producono valori NULL quando non ci sono dati per una colonna specifica. Questi possono rallentare le query e rendere i risultati più difficili da interpretare. Per evitare questo problema, utilizzare funzioni come COALESCE per sostituire i valori NULL con un valore predefinito.

• Test con dati di esempio: Le query di pivot possono comportarsi in modo diverso con grandi set di dati a causa dell’aumento della memoria e delle richieste di elaborazione. Testare sempre le query di pivot su dati reali o campioni rappresentativi per valutare con precisione gli impatti sulle prestazioni.

Prova il nostro percorso professionale SQL Server Developer, che copre tutto, dalle transazioni e dalla gestione degli errori al miglioramento delle prestazioni delle query.

Implementazioni Specifiche del Database

Le operazioni di pivot differiscono significativamente tra database come SQL Server, MySQL e Oracle. Ciascuno di questi database ha una sintassi e limitazioni specifiche. Illustrerò esempi di pivot dei dati nei diversi database e le loro funzionalità principali.

SQL Server

SQL Server fornisce un operatore PIVOT integrato, che è semplice da usare per trasformare righe in colonne. L’operatore PIVOT è facile da utilizzare e si integra con le potenti funzioni di aggregazione di SQL Server. Le principali caratteristiche del pivoting in SQL includono le seguenti:

• Supporto diretto per PIVOT e UNPIVOT: L’operatore PIVOT di SQL Server consente una rapida trasformazione da righe a colonne. Anche l’operatore UNPIVOT può invertire questo processo.

• Opzioni di aggregazione: L’operatore PIVOT consente varie funzioni di aggregazione, come SUM, COUNT e AVG.

Il limite dell’operatore PIVOT in SQL Server è che richiede che i valori delle colonne da pivotare siano noti in anticipo, rendendolo meno flessibile per dati che cambiano dinamicamente.

Nell’esempio sottostante, l’operatore PIVOT converte i valori Product in colonne e aggrega Sales utilizzando l’operatore SUM.

-- Seleziona la Data e le colonne pivotate per ogni prodotto
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Sottoquery per recuperare le colonne Data, Prodotto e Vendite
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggrega le Vendite per Prodotto, pivotando i valori del prodotto in colonne
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Ti consiglio di seguire il corso Introduzione a SQL Server di DataCamp per padroneggiare le basi di SQL Server per l’analisi dei dati.

MySQL

MySQL non supporta nativamente l’operatore PIVOT. Tuttavia, puoi utilizzare l’istruzione CASE per pivotare manualmente le righe in colonne e combinare altre funzioni aggregate come SUM, AVG e COUNT. Sebbene questo metodo sia flessibile, può diventare complesso se hai molte colonne da pivotare.

La query sottostante ottiene lo stesso output dell’esempio PIVOT di SQL Server aggregando condizionalmente le vendite per ogni prodotto utilizzando l’istruzione CASE.

-- Seleziona data e colonne pivot per ogni prodotto
SELECT 
    Date,
    -- Usa CASE per creare una colonna per le vendite di Laptop e Mouse
    SUM(CASE WHEN Product = 'Laptop' THEN Sales ELSE 0 END) AS Laptop,
    SUM(CASE WHEN Product = 'Mouse' THEN Sales ELSE 0 END) AS Mouse
FROM SalesData
GROUP BY Date;

Oracle

Oracle supporta l’operatore PIVOT, che consente la trasformazione diretta delle righe in colonne. Proprio come in SQL Server, sarà necessario specificare esplicitamente le colonne da trasformare.

Nella query sottostante, l’operatore PIVOT converte i valori di ProductName in colonne e aggrega SalesAmount utilizzando l’operatore SUM.

SELECT *
FROM (
    -- Selezione dei dati di origine
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- Aggrega le vendite per prodotto, creando colonne pivotate
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

Esempio di trasformazione dell’output utilizzando l’operatore PIVOT SQL in Oracle. Immagine di Autore.

Tecniche Avanzate per Trasformare Righe in Colonne in SQL

Tecniche avanzate per trasformare righe in colonne sono utili quando hai bisogno di flessibilità nel gestire dati complessi. Tecniche dinamiche e gestione di più colonne contemporaneamente ti consentono di trasformare dati in scenari in cui il pivoting statico è limitato. Esploriamo questi due metodi nel dettaglio.

Pivot dinamici

I pivot dinamici ti consentono di creare query pivot che si adattano automaticamente ai cambiamenti nei dati. Questa tecnica è particolarmente utile quando hai colonne che cambiano frequentemente, come i nomi dei prodotti o le categorie, e vuoi che la tua query includa automaticamente nuove voci senza aggiornarla manualmente.

Supponiamo di avere una tabella SalesData e di poter creare un pivot dinamico che si adatta se vengono aggiunti nuovi prodotti. Nella query seguente, @columns costruisce dinamicamente l’elenco delle colonne pivotate e sp_executesql esegue il SQL generato.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- Passo 1: Generare un elenco di prodotti distinti da pivotare
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- Passo 2: Costruire la query SQL dinamica
SET @sql = N'
SELECT Date, ' + @columns + '
FROM 
    (SELECT Date, Product, Sales FROM SalesData) AS source
PIVOT
(
    SUM(Sales)
    FOR Product IN (' + @columns + ')
) AS pivot_table;';
-- Passo 3: Eseguire il SQL dinamico
EXEC sp_executesql @sql;

Gestione di colonne multiple

In scenari in cui è necessario pivotare contemporaneamente più colonne, si utilizzerà l’operatore PIVOT e tecniche di aggregazione aggiuntive per creare più colonne nella stessa query.

Nell’esempio seguente, ho pivotato le colonne Sales e Quantity per Product.

-- Vendite e Quantità per Laptop e Mouse per Data
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        -- Pivot per le Vendite
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Sales FROM SalesData) AS source
        PIVOT
            (SUM(Sales) FOR Product IN ([Laptop], [Mouse])) AS pivot_sales
    ) p1
JOIN
    (
        -- Pivot per la Quantità
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Quantity FROM SalesData) AS source
        PIVOT
            (SUM(Quantity) FOR Product IN ([Laptop], [Mouse])) AS pivot_quantity
    ) p2
ON p1.Date = p2.Date;

Trasformazione dell’output di esempio di colonne multiple utilizzando l’operatore PIVOT SQL. Immagine dell’Autore.

La creazione di pivot per colonne multiple consente di ottenere report più dettagliati pivotando più attributi per articolo, consentendo di ottenere analisi più approfondite. Tuttavia, la sintassi può essere complessa, specialmente se esistono molte colonne. Potrebbe essere necessario inserire manualmente i valori a meno che non vengano combinati con tecniche di pivot dinamico, aggiungendo ulteriore complessità.

Conclusione

La trasformazione da righe a colonne è una tecnica SQL che vale la pena apprendere. Ho visto le tecniche di pivot SQL utilizzate per creare una tabella di retention di coorti, in cui è possibile tracciare la retention degli utenti nel tempo. Ho anche visto le tecniche di pivot SQL utilizzate nell’analisi dei dati di un sondaggio, in cui ogni riga rappresenta un partecipante e ogni domanda può essere trasformata in una colonna.

Il nostro corso Reporting in SQL è un’ottima opzione se desideri approfondire la sintesi e la preparazione dei dati per la presentazione e/o la creazione di dashboard. I nostri percorsi professionali Associate Data Analyst in SQL e Associate Data Engineer in SQL sono un’altra ottima idea e arricchiscono qualsiasi curriculum, quindi iscriviti oggi.