Visualizza una situazione in cui i dati vogliono essere visualizzati con un orientamento diverso da quello in cui i dati vengono archiviati, con le condizioni del layout di righe e colonne. Il processo di trasformazione dei dati da riga a colonna è chiamato pivot . Gli operatori PIVOT e UNPIVOT in SQL Server consentono di modificare l’orientamento dei dati da orientato per colonna a orientato per riga e viceversa. Questo viene fatto unendo i valori contenuti in una colonna in un elenco di valori distinti e quindi proiettando quell’elenco come intestazione di colonna.

Operatore PIVOT

Documenti: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

Sintassi:

SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name> FROM (<SELECT query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>;

Per dirla più semplicemente, per usare PIVOT abbiamo bisogno di 3 componenti nella sintassi:

• Nella clausola FROM deve essere sempre fornita la colonna di input, l’operatore PIVOT utilizza queste colonne per identificare quali colonne devono essere utilizzate per raggruppare i dati per l’aggregazione.
• Il separatore di virgola visualizzato nei dati del feed verrà utilizzato come intestazione di colonna per i dati pivot.
• Con una funzione di aggregazione, come SUM, utilizzata per eseguire calcoli e raggruppare i record.

Per esempio:

Considerando la situazione seguente, quando si vuole ottenere il costo medio dei giorni di riparazione nei casi di 1,2,3,4 giorni, se si utilizza normalmente GROUP BY la sintassi sarà la seguente:

USE AdventureWorks2019 ; GO SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture;

Ciò si traduce in un record DaysToManufacturing mancante di 3, perché nel caso di 3 i prezzi COST non possono essere calcolati in media perché non ci sono dati. Questo problema può essere gestito tramite PIVOT come segue:

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4] FROM ( SELECT DaysToManufacture, StandardCost FROM Production.Product ) AS SourceTable PIVOT ( AVG(StandardCost) FOR DaysToManufacture IN ([0], [1], [2], [3], [4]) ) AS PivotTable;

Esempio 2:

Ottieni i primi 5 totali SalesYTD della tabella Sales.SalesTerritory, come segue:

SELECT TOP 5 SUM(SalesYTD) AS TotalSalesYTD, Name FROM Sales.SalesTerritory GROUP BY Name

E poiché i dati sono piccoli, mostrano solo il valore di ciascuna area, quindi possiamo distribuire i dati orizzontalmente in modo che quando si interroga il database, il database restituirà solo una riga, aumentando la capacità di lettura e analisi per il database. persona:

SELECT TOP 5 'TotalSalesYTD' AS GrandTotal, [NorthWest],[NorthEast],[Central],[Southwest],[Southeast] FROM (SELECT TOP 5 Name,SalesYTD FROM Sales.SalesTerritory) AS SourceTable PIVOT (SUM(SalesYTD) FOR Name IN ([NorthWest],[NorthEast],[Central],[Southwest],[Southeast])) AS PivotTable;

Una delle principali sfide nella scrittura di query utilizzando PIVOT è il requisito di fornire un elenco fisso di elementi di diffusione per l’operatore PIVOT. Non è fattibile o impraticabile farlo per un gran numero di elementi di diffusione. Per ovviare a questo, gli sviluppatori possono utilizzare SQL dinamico. Dynamic SQL fornisce una soluzione per costruire una stringa letterale che viene passata a SQL Server, interpretata come un comando e quindi eseguita da SQL Server.

Operatore UNPIVOT

L’operatore UNPIVOT è inteso come l’opposto di PIVOT , che significa ruotare l’asse da una colonna all’altra. Unpivot non memorizzerà il valore originale, i dati dettagliati sono andati persi durante l’aggregazione durante il pivot. UNPIVOT non ha la capacità di allocare un valore per restituire i dati di dettaglio originali. Invece di convertire le righe in colonne, il risultato di unpivot converte le colonne in righe. SQL Server fornisce l’operatore UNPIVOT per convertire i dati dalle tabelle pivot all’orientamento basato su riga.

Durante l’annullamento del pivot dei dati, una o più colonne definite come origine verranno convertite in righe. I dati in queste righe vengono distribuiti o suddivisi da una a più righe, in base al numero di colonne necessarie senza pivot.

Per esempio:

CREATE TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT, Emp3 INT, Emp4 INT, Emp5 INT); GO INSERT INTO pvt VALUES (1,4,3,5,4,4); INSERT INTO pvt VALUES (2,4,1,5,5,5); INSERT INTO pvt VALUES (3,4,3,5,4,4); INSERT INTO pvt VALUES (4,4,2,5,5,4); INSERT INTO pvt VALUES (5,5,1,5,5,5); GO -- Unpivot the table. SELECT VendorID, Employee, Orders FROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p UNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) )AS unpvt; GO