Questo articolo fornirà una panoramica completa dell'utilizzo degli operatori PIVOT e UNPIVOT in SQL Server. Gli operatori PIVOT e UNPIVOT sono simili agli operatori relazionali che consentono trasformando l'espressione con valori di tabella in un'altra tabella . Entrambi gli operatori generano report multidimensionali che aiutano a combinare e confrontare rapidamente una grande quantità di dati.
Possiamo usare il Operatore PIVOT quando dobbiamo trasformare le espressioni con valori di tabella. Divide il valori univoci da una colonna a molte colonne nel risultato finale. Anche aggregati i restanti valori della colonna richiesti nel risultato finale. Operatore UNPIVOT converte i dati dalle colonne di un'espressione con valori di tabella in valori di colonna, che è l'inverso di PIVOT.
Cerchiamo di capirlo con l'aiuto del semplice diagramma riportato di seguito:
100 kmh in mph
Sul lato sinistro di questa figura, possiamo vedere il set di dati originale , che ha tre colonne: Anno, Regione, E Saldi . Successivamente, possiamo vedere la tabella PIVOT sul lato destro, che viene costruita ruotando il Regione (righe) in Nord e Sud (colonne) . Dopo aver convertito le righe in colonne, possiamo eseguire un'operazione aggregato dei valori della colonna Vendite per ogni intersezione tra le colonne e le righe della tabella PIVOT.
Creiamo innanzitutto una tabella denominata pivot_demo per dimostrare gli operatori PIVOT e UNPIVOT. La seguente istruzione crea una nuova tabella nel nostro database specificato:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Successivamente, inserisci alcuni dati in questa tabella come di seguito:
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
Possiamo verificare i dati utilizzando l'istruzione SELECT. Otterremo l'output seguente:
Operatore PIVOT
Questo operatore viene utilizzato per ruotare le espressioni con valori di tabella. È stato introdotto per la prima volta nella versione SQL Server 2005. Converte i dati da righe a colonne. Suddivide i valori univoci di una colonna in molte colonne e quindi aggrega i valori di colonna rimanenti richiesti nel risultato finale.
Dobbiamo seguire i seguenti passaggi per creare una tabella PIVOT:
- Seleziona il set di dati di base per il pivot.
- Crea risultati temporanei con l'aiuto di una tabella derivata o CTE (espressione di tabella comune).
- Utilizzare l'operatore PIVOT.
Sintassi
La sintassi seguente illustra l'utilizzo di PIVOT in SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Se rompiamo questo script, possiamo vedere che ha due sezioni separate. La prima sezione seleziona i dati dalla tabella principale e la seconda sezione determina come verrà costruita la tabella PIVOT. La seconda parte contiene anche alcune parole chiave speciali come SUM, FOR e IN. Vediamo il significato di queste parole chiave nell'operatore PIVOT.
tabella ascii java
SOMMA
Questo operatore è abituato aggregare i valori dalla colonna specificata da utilizzare nella tabella PIVOT. Dobbiamo usarlo con l'operatore PIVOT per ottenere la visualizzazione di colonne aggregate per le sezioni dei valori.
PER Parola chiave
Questa parola chiave viene utilizzata per l'istruzione della tabella PIVOT istruire l'operatore PIVOT su quale colonna applicare la funzione PIVOT. Fondamentalmente, indica i nomi delle colonne che si trasformeranno da righe a colonne.
IN Parola chiave
Questa parola chiave elenca tutti i valori univoci dalla colonna PIVOT da visualizzare come colonne della tabella PIVOT.
Esempio
Cerchiamo di capirlo con l'aiuto di vari esempi.
1. La seguente istruzione seleziona innanzitutto le colonne Anno, Nord e Sud come dati di base per il pivot. Quindi, crea un risultato temporaneo utilizzando la tabella derivata e infine applica l'operatore PIVOT per generare l'output finale. Anche questa uscita è ordinata in ordine ascendente.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
L'esecuzione di questa istruzione produrrà l'output seguente. Qui possiamo vedere il somma calcolata delle vendite delle regioni Nord e Sud corrispondenti ai valori dell'anno .
stivale primaverile
2. Questo è un altro esempio in cui calcoleremo la somma delle vendite per ogni anno corrispondente ai valori della regione:
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
L'esecuzione di questa istruzione lo farà produrre un errore perché non possiamo specificare direttamente il valore numerico come nome di colonna.
Tuttavia, SQL Server ci consente di evitare questo problema utilizzando le parentesi prima di ciascun valore intero. L'istruzione aggiornata è mostrata nel seguente frammento di codice:
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
Questa dichiarazione è stata eseguita correttamente e visualizza la somma calcolata delle vendite per ogni anno corrispondente ai valori della regione:
3. L'esempio precedente di come ottenere una tabella PIVOT è utile quando siamo a conoscenza di tutti i possibili valori delle colonne PIVOT. Ma supponiamo che nel prossimo anno il numero di colonne venga aumentato. Considerando l'esempio precedente, abbiamo gli anni 2010, 2011 e 2012 come colonne PIVOT. Tuttavia, non vi è alcuna garanzia che queste colonne non subiranno modifiche in futuro. Cosa succede se abbiamo dati del 2013 o del 2014, o forse anche di più? In questi casi, dovremo utilizzare tabella PIVOT dinamica query per risolvere questo problema.
La query della tabella PIVOT dinamica incapsula l'intero script PIVOT in una procedura memorizzata. Questa procedura fornirà opzioni regolabili, permettendoci di modificare i nostri requisiti modificando alcuni valori parametrizzati.
Il seguente codice SQL spiega il funzionamento della tabella PIVOT dinamica. In questo script, abbiamo prima recuperato tutti i valori distinti dalla colonna PIVOT e quindi scritto un'istruzione SQL da eseguire con la query PIVOT in fase di esecuzione. Vediamo l'output dopo aver eseguito questo script:
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
In questo script abbiamo creato due variabili parametrizzate. La sua descrizione è riportata di seguito:
@PivotColumn : questa variabile prenderà il nome della colonna dalla tabella originale su cui viene creata la tabella PIVOT. Per esempio , qui la colonna 'Regione' mostra tutte le regioni disponibili nelle colonne.
@PivotList : questa variabile prenderà l'elenco di colonne che vogliamo visualizzare come colonna di output nella tabella PIVOT.
Esecuzione di Stored Procedure Dinamiche
Dopo aver creato con successo la procedura memorizzata dinamica, siamo pronti per eseguirla. La seguente istruzione viene utilizzata per chiamare la procedura memorizzata dinamica per visualizzare la tabella PIVOT in fase di esecuzione:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Qui ora abbiamo specificato il nome della colonna ' Regione ' come primo parametro e l'elenco delle colonne PIVOT come secondo parametro. L'esecuzione dello script visualizzerà il seguente output:
dimensione del vettore c++
Ora possiamo aggiungere più colonne in futuro in fase di runtime per visualizzare la tabella PIVOT, cosa che non è possibile con i primi due esempi.
Operatore UNPIVOT
È il metodo inverso dell'operatore PIVOT in SQL Server. Manca questo operatore esegue il funzionamento opposto di PIVOT convertendo i dati dalle colonne alle righe. L'operatore UNPIVOT ruota anche la tabella PIVOT nella tabella normale. È stato introdotto per la prima volta nella versione SQL Server 2005.
Sintassi
La sintassi seguente illustra UNPIVOT in SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Esempio
Cerchiamo di capire come UNPIVOT l'operazione PIVOT con degli esempi. Per prima cosa creeremo un file tavolo originale e tavolo PIVOT e quindi applicato l'operatore UNPIVOT su questa tabella.
Il seguente frammento di codice dichiara innanzitutto una variabile di tabella temporanea @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Successivamente, inseriremo i valori in questa tabella come di seguito:
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
Ora possiamo eseguire l'operazione UNPIVOT utilizzando la seguente istruzione:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
L'esecuzione dello snippet di codice restituirà il seguente output:
Il frammento di codice seguente è un altro esempio per eseguire prima l'operazione PIVOT e poi l'operazione UNPIVOT sulla stessa tabella all'interno di una singola query:
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
L'esecuzione dello snippet di codice visualizzerà lo stesso output:
if else istruzioni java
NOTA: il processo UNPIVOT è un'operazione inversa della procedura PIVOT, ma non è un'esatta inversione. Poiché le righe sono state unite quando PIVOT calcola l'aggregato e ha combinato molte righe in un'unica riga nel risultato, l'operazione UNPIVOT non può rendere la tabella come quella originale. Tuttavia, se l'operatore PIVOT non unisce molte righe in un'unica riga, l'operatore UNPIVOT può ottenere la tabella originale dall'output PIVOT.
Conclusione
Questo articolo fornirà una panoramica completa degli operatori PIVOT e UNPIVOT in SQL Server e convertirà un'espressione di tabella in un'altra. Non bisogna mai dimenticare che UNPIVOT è l'operazione inversa di PIVOT, ma non è l'esatto contrario del risultato PIVOT.