Utilizzo di PIVOT e UNPIVOT
È possibile utilizzare gli operazioni relazionali PIVOT e UNPIVOT per modificare un'espressione valutata a livello di tabella in un'altra tabella. PIVOT ruota un'espressione valutata a livello di tabella convertendo i valori univoci da una colonna nell'espressione in più colonne nell'output ed esegue aggregazioni dove sono necessarie sui valori di colonna restanti da includere nell'output finale. L'operatore UNPIVOT esegue l'operazione contraria rispetto a PIVOT ruotando le colonne di un'espressione valutata a livello di tabella in valori colonna.
Nota
Quando si utilizza la parola chiave PIVOT e UNPIVOT sui database aggiornati a SQL Server 2005 o versioni successive, è necessario che il livello di compatibilità del database sia impostato su 90 o un valore superiore. Per informazioni sull'impostazione delle proprietà del livello di compatibilità del database, vedere sp_dbcmptlevel (Transact-SQL).
La sintassi per PIVOT è più semplice e leggibile di quella che potrebbe essere altrimenti specificata in una serie complessa di istruzioni SELECT...CASE. Per una descrizione completa della sintassi per PIVOT, vedere FROM (Transact-SQL).
La sintassi con annotazioni per PIVOT è la seguente.
SELECT <colonna non trasformata tramite Pivot>,
[prima colonna trasformata tramite Pivot] AS <nome colonna>,
[seconda colonna trasformata tramite Pivot] AS <nome colonna>,
...
[ultima colonna trasformata tramite Pivot] AS <nome colonna>,
FROM
(<query SELECT che produce i dati>)
AS <alias della query di origine>
PIVOT
(
<funzione di aggregazione>(<colonna in fase di aggregazione>)
FOR
[<colonna contenente i valori che diventeranno intestazioni di colonna>]
IN ( [prima colonna trasformata tramite Pivot], [seconda colonna trasformata tramite Pivot],
... [ultima colonna trasformata tramite Pivot])
) AS <alias della tabella pivot>
<clausola ORDER BY facoltativa>;
Esempio di PIVOT di base
Nell'esempio di codice seguente viene generata una tabella a due colonne che include quattro righe.
USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Set di risultati:
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
Nessun prodotto viene definito con tre 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. Una colonna è disponibile per tre [3] giorni, anche se i risultati sono NULL.
-- Pivot table with one row and five columns
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;
Set di risultati:
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Esempio di PIVOT complesso
Uno scenario comune in cui PIVOT può essere utile è il caso in cui si desidera generare report a tabulazione incrociata per creare un riepilogo dei dati. Si supponga, ad esempio, di voler eseguire una query sulla tabella PurchaseOrderHeader nel database di esempio AdventureWorks2008R2 per determinare il numero di ordini di acquisto effettuati da dipendenti specifici. La query seguente fornisce questo report, ordinato per fornitore.
USE AdventureWorks2008R2;
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;
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;
In questo modo, i valori univoci restituiti dalla colonna EmployeeID diventano campi nel set di risultati finale. Pertanto è presente una colonna per ogni numero EmployeeID specificato nella clausola Pivot. In questo caso i dipendenti 164, 198, 223, 231 e 233. 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. Si noti che viene visualizzato un messaggio di avviso che indica che eventuali valori Null visualizzati nella colonna PurchaseOrderID non sono considerati nel calcolo del COUNT per ogni dipendente.
.gif "Nota importante") Importante |
|---|
Quando le funzioni di aggregazione sono utilizzate con PIVOT, gli eventuali valori Null presenti nella colonna dei valori non vengono considerati nel calcolo di un'aggregazione. |
L'operatore UNPIVOT esegue l'operazione contraria 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. Ciò significa che è necessario identificare altre due colonne. La colonna che includerà i valori di colonna da ruotare (Emp1, Emp2...) sarà denominata Employee e la colonna che includerà i valori che attualmente si trovano nelle colonne da ruotare sarà denominata Orders. Tali colonne corrispondono rispettivamente a pivot_column e value_column nella definizione Transact-SQL. La query è la seguente.
--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
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
...
Si noti che UNPIVOT non è l'esatto opposto di PIVOT. PIVOT esegue un'aggregazione e, pertanto, unisce più righe possibili in una riga singola nell'output. L'operatore UNPIVOT non riproduce il risultato dell'espressione valutata a livello di tabella originale perché le righe sono state unite. Inoltre, i valori Null nell'input di UNPIVOT vengono esclusi dall'output, mentre è possibile che fossero presenti valori Null originali nell'input prima dell'operazione PIVOT.
La vista Sales.vSalesPersonSalesByFiscalYears nel database di esempio AdventureWorks2008R2 utilizza PIVOT per restituire le vendite totali per ogni venditore, per ogni anno fiscale. Per creare uno script per la vista in SQL Server Management Studio, in Esplora oggetti individuare la vista nella cartella relativa alle viste per il database AdventureWorks2008R2. Fare clic con il pulsante destro del mouse sul nome della vista e quindi selezionare Crea script per vista.