Utilizzo di PIVOT e UNPIVOT

Esempio di PIVOT di base

Nell'esempio di codice seguente viene generata una tabella a due colonne che include quattro righe.

USE AdventureWorks ;
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 AdventureWorks per determinare il numero di ordini di acquisto effettuati da dipendenti specifici. La query seguente fornisce questo report, ordinato per fornitore.

USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY pvt.VendorID;

Set di risultati parziale:

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
1           4           3           5           4           4
2           4           1           5           5           5
3           4           3           5           4           4
4           4           2           5           5           4
5           5           1           5           5           5

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.

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 possibili righe multiple 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 AdventureWorks 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 AdventureWorks. Fare clic con il pulsante destro del mouse sul nome della vista e quindi selezionare Crea script per vista.

Vedere anche

Riferimento