In questo articolo, vedremo gli operatori SQL Pivot e SQL Unpivot e come possono essere utili per trasporre i dati di SQL Server. Inoltre, discuteremo sia i modi statici che dinamici per utilizzare gli operatori relazionali PIVOT e UNPIVOT che possono essere utilizzati per trasformare valori distinti aggregati come colonne nel set di risultati specificando tutti i valori di colonna nella clausola PIVOT IN.
SQL Server pivot Introduzione
Esistono diversi modi per trasporre un set di dati da righe a colonne e colonne a righe. SQL Pivot è una delle tecniche che consente la trasposizione di righe in colonne ed esegue possibili aggregazioni lungo il percorso. Gli operatori relazionali SQL PIVOT e SQL UNPIVOT traspongono dati bidimensionali a valori di tabella in un’altra forma di dati. SQL PIVOT traspone un’espressione con valori di tabella da un insieme univoco di valori da una colonna in più colonne nell’output ed esegue le aggregazioni. SQL UNPIVOT esegue l’operazione opposta di SQL PIVOT trasformando le colonne di un’espressione con valori di tabella in valori di colonna.
Nella maggior parte dei casi, la tecnica di pivoting statico è sufficiente per il requisito aziendale. Ad esempio, la previsione delle vendite mensili, la divisione delle vendite annuali, l’aggregazione delle vendite trimestrali, ecc., in cui le colonne della clausola IN rimangono statiche. In alcuni altri casi, abbiamo bisogno di dettagli granulari e l’espressione del valore della tabella è di natura più dinamica e per tutto il tempo il nuovo set dell’espressione è incluso nella tabella, quindi il PIVOT dinamico sarebbe la scelta migliore.
Nota: Nella rappresentazione di cui sopra, possiamo vedere che nel processo di PIVOT, i valori della colonna vengono ruotati da verticale a orizzontale e UNPIVOTING è come ruotarlo da orizzontale a verticale.
Syntax
SELECT <non-pivoted column>,
AS <column alias>,
AS <column alias>,
…
AS <column alias>
FROM
(
<SELECT QUERY>)
AS <Alias for temporary data set>
PIVOT
(
<Aggregate function>( Aggregate column)
FOR
IN ( , ,
… )
) AS <tabella Pivot alias>
< clausola ORDER BY>;
come iniziare con SQL Pivot
stiamo per iniziare con basculante e onu-basculante dati. Ormai capisci quali operatori PIVOT e UNPIVOT sono in SQL Server. In termini semplici, è solo un altro modo per descrivere il processo di trasformazione di valori di riga distinti nelle proprie colonne è noto come Pivoting. E il processo di trasformare le colonne in righe è noto come un non-pivoting.
Esempio 1: Come eseguire un’operazione SQL Pivot
Immaginiamo il set di dati di esempio derivato dal database AdventureWorks2014. In questo caso, hai appena ricevuto un semplice set di dati con la colonna SalesYear lungo un valore quantitativo TotalSales.
Ora, il set di dati è pronto per il pivoting. Hai SalesYear nella prima colonna, TotalSales nella seconda colonna. Se hai chiesto di trasporre i dati, devi prendere ogni anno distinto nella colonna e queste colonne diventano l’intestazione delle colonne pivot. Quindi, il 2011 diventa la prima colonna, il 2012 diventa la sua colonna e così via. I valori effettivi della colonna successiva Totalsales, si attaccano alle intestazioni delle colonne pivot. Ora, la tabella pivotante sarebbe simile al di sotto.
1
2
3
4
5
6
7
8
9
10
|
SELECT * FROM
(
SELEZIONARE l’ANNO(SOH.OrderDate) come SalesYear,
SOH.Totale parziale come TotalSales
DALLE vendite.SalesOrderHeader SOH
PARTECIPA vendite.SalesOrderDetail SOD SU SOH.SalesOrderId = SOD.SalesOrderId
) COME Vendita
PIVOT (SUM(TotalSales)
PER SalesYear IN (,,,))
come PVT
|
L’immagine seguente mostra i dati di input, Pivot di sintassi, SQL Pivot, e risultati di output.
Esempio 2: Come eseguire un’operazione SQL Unpivot
Diamo ora un’occhiata nell’altra direzione, un-pivoting, che è semplice come andare da orizzontale a verticale. In questo caso, prenderesti tutte quelle colonne distinte selezionate e trasformerebbe quelle intestazioni di colonna nelle proprie righe. Ruotare è come ruotare da verticale a orizzontale. E non-pivoting è come ruotare da orizzontale a verticale.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SELEZIONARE SalesYear,
TotalSales
DAL
(
SELECT *
DAL
(
SELEZIONARE l’ANNO(SOH.OrderDate) COME SalesYear,
SOH.Totale parziale COME TotalSales
DALLE vendite.SalesOrderHeader SOH
PARTECIPA vendite.SalesOrderDetail SOD SU SOH.SalesOrderId = SOD.SalesOrderId
) in Vendita di PIVOT(SUM(TotalSales) PER SalesYear IN(,
)) COME da PVT
) T UNPIVOT(TotalSales PER SalesYear IN(,
)) COME upvt;
|
Nell’esempio seguente, il pivot set di dati viene utilizzato per onu-pivot valori. Il pezzo chiave qui è che è stato facile da fare per essere trasformati perché siamo stati in grado di creare una tabella pivot come passaggio intermedio e quindi eseguire la trasformazione utilizzando l’operazione unpivot.
Nota: SQL operazione pivot risultati nel recepire il risultato aggregato in colonna, ma considerando che SQL unpivot non è un’inversione esatta di recepimento colonne in segregata valori di righe. L’operatore unpivot non dividerà i risultati aggregati.
Esempio 3: Come ottenere vendite trimestrali utilizzando SQL pivot operation
Vediamo alcuni altri esempi per comprendere meglio i concetti di pivoting. In questo caso, vedremo come ottenere dati di vendita trimestrali aggregati in base alle vendite trimestrali.
Prima di passare alla soluzione, si consiglia sempre di lavorare sulla creazione di un set di dati per l’operazione PIVOT.
L’esempio seguente fornisce le vendite mensili aggregate distribuite sull’anno di vendita.
1
2
3
4
5
6
7
8
9
|
SELEZIONARE l’ANNO(SOH.OrderDate) COME SalesYear,
MESE (SOH.OrderDate) COME SalesMonth,
SUM(SOH.Totale parziale) COME TotalSales
DALLE vendite.SalesOrderHeader SOH
PARTECIPA vendite.SalesOrderDetail SOD SU SOH.SalesOrderId = SOD.SalesOrderId
GRUPPO PER ANNO(SOH.OrderDate),
MESE (SOH.OrderDate)
ORDINE PER ANNO (SOH.OrderDate),
MESE (SOH.Data ordine);
|
L’output seguente mostra i dati di base
una Volta che siete pronti con la base di dati, è possibile applicare l’operatore PIVOT. Nell’esempio seguente, la clausola IN accetta un carattere non numerico generato in base al valore DATEPART. Il valore DATEPART viene concatenato con ‘ Q ‘ e quindi i valori vengono inviati alla clausola dell’operatore PIVOT.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SELEZIONARE SalesYear,
ISNULL(, 0) in Q1,
ISNULL(, 0) COME Q2,
ISNULL(, 0) COME Q3,
ISNULL(, 0) COME T4,
(ISNULL(, 0) + ISNULL(, 0) + ISNULL (, 0) + ISNULL (, 0)) SalesYTD
DA
(
SELEZIONA ANNO (SOH.Se HAI bisogno di aiuto, non esitare A contattarci.Ordine) COME VARCHAR(1)) COME VARCHAR(2)) Quarti,
SOH.Totale parziale COME TotalSales
DALLE vendite.SalesOrderHeader SOH
PARTECIPA vendite.SalesOrderDetail SOD SU SOH.SalesOrderId = SOD.SalesOrderId
) AS Data PIVOT(SUM (TotalSales) FOR Quarters IN(,
,
,
)) AS pvt
ORDER BY SalesYear;
|
Il seguente output è un trimestrale di divisione dei dati di vendita
Esempio 4: Come ottenere vendite mensili utilizzando SQL operazione pivot
diamo uno sguardo in un altro esempio di vedere le vendite per dividere i dati in base a ogni mese. Nell’esempio seguente, i valori della clausola IN vengono generati come parte della funzione DATEPART. I valori DATEPART sono alimentati alla clausola IN dell’operatore PIVOT.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SELEZIONARE SalesYear,
Isnull(, 0), come Jan,
Isnull(, 0) Feb,
Isnull(, 0) il Mar,
ISNULL(, 0) come Apr,
isnull(, 0), come
isnull(, 0) come Jun,
isnull(, 0) come Lug,
isnull(, 0) come Ago,
isnull(, 0) come Set,
ISNULL(, 0) come Strumento,
isnull(, 0) come Nov,
isnull(, 0) in dicembre,
(isnull(, 0) + isnull(, 0) + ISNULL(, 0) + isnull(, 0) + isnull(, 0) + isnull(, 0) + ISNULL(, 0) + ISNULL(, 0) + isnull(, 0) + isnull(, 0) +isnull (, 0) + isnull (, 0) + isnull (, 0) + ISNULL (, 0) + ISNULL(, 0) + ISNULL(, 0)) SalesYTD
DAL
(
SELEZIONARE ANNO(SOH.OrderDate) COME SalesYear,
DATEPART (MESE, SOH.OrderDate) Mesi,
SOH.Totale parziale COME TotalSales
DALLE vendite.SalesOrderHeader SOH
PARTECIPA vendite.SalesOrderDetail SOD SU SOH.SalesOrderId = SOD.SalesOrderId
) COME PIVOT(SUM(TotalSales) PER Mesi(,
,
)) COME da pvt;
|
Il seguente output è un mensile di divisione dei dati di vendita
Esempio 5: Come ottenere vendite mensili utilizzando SQL dinamico operazione pivot
Cerchiamo di indirizzo mensile split dei dati di vendita utilizzando la dinamica basculante. Finora, abbiamo discusso le operazioni di pivot statici. Per convertire un’operazione pivot statica in dinamica, dobbiamo rimuovere i valori hardcoded dalla clausola IN. Innanzitutto, ottenere l’espressione distinta dal set di dati e quindi preparare una stringa concatenando tutte le espressioni. Nell’esempio seguente, il @ columnname viene utilizzato per concatenare tutta l’espressione. L’espressione concatenata viene alimentata alla clausola pivot IN. Il resto è una semplice conversione di SQL statico in un SQL dinamico e chiamare @ DML utilizzando sp_executesql stored procedure.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
AFFERMANDO @dml COME NVARCHAR(MAX)
DICHIARATO @ColumnName COME NVARCHAR (MAX)
SELEZIONA @ColumnName= ISNULL(@ColumnName+’,’,”)
+ QUOTENAME(Months)
FROM (SELEZIONA DISTINCT DATEPART(MONTH, SOH.OrderDate) Mesi
DALLE vendite.SalesOrderHeader SOH
PARTECIPA vendite.SalesOrderDetail SOD SU SOH.SalesOrderId = SOD.SalesOrderId
GRUPPO PER ANNO(SOH.OrderDate),
DATEPART (MESE, SOH.OrderDate)) AS Months
Prepare Prepara la query PIVOT usando la dinamica
IMPOSTA @dml =
N’SELECT SalesYear, ‘+@ColumnName + ‘ DA
(
SELEZIONA ANNO(SOH.OrderDate) COME SalesYear,
DATEPART (MESE, SOH.OrderDate) Mesi,
SOMMA(SOH.Totale parziale) COME TotalSales
DALLE vendite.SalesOrderHeader SOH
PARTECIPA vendite.SalesOrderDetail SOD SU SOH.SalesOrderId = SOD.SalesOrderId
GRUPPO PER ANNO(SOH.OrderDate),
DATEPART (MESE, SOH.OrderDate)) COME T
PIVOT(SUM(TotalSales)
PER Mesi IN (‘ + @ColumnName + ‘)) COME PVTTable’
–Print @DynamicPivotQuery
–Esegui la Dinamica Perno Query
EXEC sp_executesql @dml
|
Questo è tutto per ora…
conclusioni
finora, abbiamo dimostrato i concetti di base e diversi esempi di SQL PIVOT e SQL UNPIVOT.
L’esempio è basato sul database adventureworks2014 di SQL Server. Si può dare una prova e testare tutti i campioni da soli. Se hai qualche domanda, sentitevi liberi di commentare qui sotto…
- Autore
- Post Recenti
La mia specialità consiste nel progettare & implementando soluzioni ad alta disponibilità e migrazione DB multipiattaforma. Le tecnologie attualmente in lavorazione sono SQL Server, PowerShell, Oracle e MongoDB.
Visualizza tutti i messaggi di Prashanth Jayaram
- Una rapida panoramica di controllo del database in SQL – gennaio 28, 2021
- Come impostare la Sincronizzazione di Dati Azure tra Azure SQL database e di SQL Server-edificio – 20 gennaio 2021
- Come eseguire Azure SQL database di Importazione/Esportazione, le operazioni di utilizzo di PowerShell – 14 gennaio 2021