FROM - Uso di PIVOT e UNPIVOT
Si applica a:
SQL Server database SQL di Azure Istanza gestita di SQL di Azure endpoint di analisi SQL di Azure Synapse Analytics Platform System (PDW) in Microsoft Fabric Warehouse nel database SQL di Microsoft Fabric in Microsoft Fabric
È possibile usare gli operatori relazionali PIVOT e UNPIVOT per modificare un'espressione con valori di tabella in un'altra tabella. PIVOT ruota un'espressione con valori di tabella convertendo i valori univoci di una colonna nell'espressione in più colonne nell'output. PIVOT esegue anche le aggregazioni in cui sono necessarie in tutti i valori di colonna rimanenti desiderati nell'output finale. UNPIVOT esegue l'operazione opposta a PIVOT, ruotando le colonne di un'espressione con valori di tabella in valori di colonna.
La sintassi per PIVOT è più semplice e più leggibile rispetto alla sintassi che potrebbe altrimenti essere specificata in una serie complessa di SELECT...CASE istruzioni. Per una descrizione completa della sintassi per PIVOT, vedere clausola FROM.
Nota
L'uso ripetuto di all'interno di PIVOT/UNPIVOT una singola istruzione T-SQL può influire negativamente sulle prestazioni delle query.
Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.
Sintassi
Questa sezione riepiloga come usare l'operatore PIVOT e UNPIVOT .
Sintassi per l'operatore PIVOT .
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <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 become column headers>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
) AS <alias for the pivot table>
[ <optional ORDER BY clause> ]
[ ; ]
Sintassi per l'operatore UNPIVOT .
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <output column for names of the pivot columns> [ AS <column name> ] , ]
[ <new output column created for values in result of the source query> [ AS <column name> ] ]
FROM
( <SELECT query that produces the data> )
AS <alias for the source query>
UNPIVOT
(
<new output column created for values in result of the source query>
FOR <output column for names of the pivot columns>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
)
[ <optional ORDER BY clause> ]
[ ; ]
Osservazioni:
Gli identificatori di colonna nella clausola UNPIVOT seguono le regole di confronto dei cataloghi.
Per database SQL di Azure, le regole di confronto sono sempre
SQL_Latin1_General_CP1_CI_AS.Per i database di SQL Server parzialmente indipendenti, le regole di confronto sono sempre
Latin1_General_100_CI_AS_KS_WS_SC.
Se la colonna è combinata con altre colonne, sarà necessaria una clausola COLLATE, ovvero COLLATE DATABASE_DEFAULT, per evitare conflitti.
Nei pool di Microsoft Fabric e Azure Synapse Analytics le query con PIVOT operatore hanno esito negativo se è presente un GROUP BY oggetto nell'output della colonna non pivot da PIVOT. Come soluzione alternativa, rimuovere la colonna non pivot da GROUP BY. I risultati della query sono gli stessi, perché questa GROUP BY clausola è duplicata.
Esempio PIVOT di base
Nell'esempio di codice seguente viene generata una tabella a due colonne che include quattro righe.
USE AdventureWorks2022;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Il set di risultati è il seguente.
DaysToManufacture AverageCost
------------------ ------------
0 5.0885
1 223.88
2 359.1082
4 949.4105
Nessun prodotto è definito con il valore per 3 DaysToManufacture.
Il codice seguente consente di visualizzare lo stesso risultato, trasformato tramite Pivot in modo che i valori di DaysToManufacture diventino le intestazioni di colonna. Viene fornita una colonna per tre ([3]) giorni, anche se i risultati sono NULL.
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS CostSortedByProductionDays,
[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;
Il set di risultati è il seguente.
CostSortedByProductionDays 0 1 2 3 4
--------------------------- ----------- ----------- ----------- ----------- -----------
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Esempio PIVOT complesso
Uno scenario comune in cui PIVOT può essere utile è il caso in cui si vuole generare report a tabulazione incrociata per fornire un riepilogo dei dati. Si supponga, ad esempio, di voler eseguire una query sulla tabella PurchaseOrderHeader nel database di esempio AdventureWorks2022 per determinare il numero di ordini di acquisto effettuati da dipendenti specifici. La query seguente fornisce questo report, ordinato per fornitore.
USE AdventureWorks2022;
GO
SELECT VendorID,
[250] AS Emp1,
[251] AS Emp2,
[256] AS Emp3,
[257] AS Emp4,
[260] AS Emp5
FROM
(
SELECT PurchaseOrderID,
EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader
) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN ([250], [251], [256], [257], [260])
) AS pvt
ORDER BY pvt.VendorID;
Di seguito è riportato un set di risultati parziale.
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
----------- ----------- ----------- ----------- ----------- -----------
1492 2 5 4 4 4
1494 2 5 4 5 4
1496 2 4 4 5 5
1498 2 5 4 4 4
1500 3 4 4 5 4
I risultati restituiti dall'istruzione di selezione secondaria vengono trasformati tramite Pivot nella colonna EmployeeID.
SELECT PurchaseOrderID,
EmployeeID,
VendorID
FROM PurchaseOrderHeader;
I valori univoci restituiti dalla colonna EmployeeID diventano campi nel set di risultati finale. Di conseguenza, è presente una colonna per ogni EmployeeID numero specificato nella clausola pivot, che sono dipendenti 250, 251256, 257, e 260 in questo esempio. La colonna PurchaseOrderID funge da colonna dei valori, rispetto alla quale vengono raggruppate le colonne restituite nell'output finale, dette colonne di raggruppamento. In questo caso, le colonne di raggruppamento vengono aggregate dalla funzione COUNT. Viene visualizzato un messaggio di avviso che indica che tutti i valori Null visualizzati nella PurchaseOrderID colonna non sono stati considerati durante il calcolo di COUNT per ogni dipendente.
Importante
Quando le funzioni di aggregazione vengono usate con PIVOT, la presenza di valori Null nella colonna value non viene considerata durante il calcolo di un'aggregazione.
Esempio UNPIVOT
UNPIVOT esegue l'operazione quasi opposta rispetto a PIVOT, ruotando le colonne in righe. Si supponga che la tabella generata nell'esempio precedente venga archiviata nel database come pvt e che si desideri ruotare gli identificatori di colonna Emp1, Emp2, Emp3, Emp4 e Emp5 in valori di riga corrispondenti a un particolare fornitore. Di conseguenza, è necessario identificare due colonne aggiuntive.
La colonna che contiene i valori di colonna ruotati (Emp1, Emp2e così via) viene chiamata Employeee la colonna che contiene i valori attualmente presenti nelle colonne ruotate viene chiamata Orders. Queste colonne corrispondono rispettivamente a pivot_column e value_column nella definizione Transact-SQL. Ecco la query.
-- Create the table and insert values as portrayed in the previous example.
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
Di seguito è riportato un set di risultati parziale.
VendorID Employee Orders
----------- ----------- ------
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
UNPIVOT non è l'esatto inverso di PIVOT. PIVOT esegue un'aggregazione e unisce più righe in una singola riga nell'output. UNPIVOT non riproduce il risultato originale dell'espressione con valori di tabella, perché le righe sono state unite. Inoltre, NULL i valori nell'input di UNPIVOT scompaiono nell'output. Quando i valori scompaiono, mostra che potrebbero essere presenti valori originali NULL nell'input prima dell'operazione PIVOT .
Nella vista Sales.vSalesPersonSalesByFiscalYears nel database di esempio AdventureWorks2022 viene usato PIVOT per restituire le vendite totali per ogni venditore, per ogni anno fiscale. Per creare script per la vista in SQL Server Management Studio, in Esplora oggetti individuare la vista nella cartella Viste per il AdventureWorks2022 database. Fare clic con il pulsante destro del mouse sul nome della vista e quindi selezionare Crea script per vista.