Utilizzo di PIVOT e UNPIVOT

Data aggiornamento: 17 luglio 2006

È 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 PIVOT e UNPIVOT sono utilizzati per database aggiornati a Microsoft SQL Server 2005, il livello di compatibilità del database deve essere impostato su 90. Per informazioni su come impostare il 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 annotata 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 genera i dati> )

    AS <alias della query di origine>

PIVOT

(

    <aggregation function>(<colonna che verrà aggregata> )

FOR

[<colonna che contiene i valori che diventeranno le 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>

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>

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 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.

ms177410.note(it-it,SQL.90).gifImportante:
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 Views 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

Altre risorse

FROM (Transact-SQL)
CASE (Transact-SQL)

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione Cronologia

17 luglio 2006

Nuovo contenuto:
  • Aggiunta di una descrizione aggiuntiva della sintassi di PIVOT.
  • Aggiunta dell'esempio di PIVOT di base