Šajā rakstā ir sniegts pilnīgs pārskats par PIVOT un UNPIVOT operatoru izmantošanu SQL Server. PIVOT un UNPIVOT operatori ir līdzīgi relāciju operatoriem, kas pieļauj tabulas vērtības izteiksmes pārveidošana citā tabulā . Abi operatori ģenerē daudzdimensiju pārskatus, kas palīdz ātri apvienot un salīdzināt lielu datu apjomu.
Mēs varam izmantot PIVOT operators kad mums ir jāpārveido tabulas vērtības izteiksmes. Tas sadala unikālas vērtības no vienas kolonnas uz daudzām kolonnām gala rezultātā. Tas arī agregāti atlikušās kolonnas vērtības, kas nepieciešamas gala rezultātā. UNPIVOT operators pārvērš datus no tabulas vērtības izteiksmes kolonnām kolonnu vērtībās, kas ir PIVOT apgrieztā vērtība.
Ļaujiet mums to saprast, izmantojot tālāk sniegto vienkāršo diagrammu:
Šī attēla kreisajā pusē mēs varam redzēt sākotnējā datu kopa , kurā ir trīs kolonnas: gads, reģions, un Pārdošana . Tālāk mēs varam redzēt PIVOT tabulu labajā pusē, kas ir izveidota, pagriežot Reģions (rindas) uz ziemeļiem un dienvidiem (kolonnas) . Pēc rindu pārvēršanas kolonnās mēs varam veikt a Pārdošanas kolonnas vērtību apkopojums katram PIVOT tabulas kolonnu un rindu krustpunktam.
Vispirms izveidosim tabulu ar nosaukumu pivot_demo lai demonstrētu PIVOT un UNPIVOT operatorus. Šis paziņojums izveido jaunu tabulu mūsu norādītajā datu bāzē:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Pēc tam ievietojiet dažus datus šajā tabulā, kā norādīts tālāk.
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);
Mēs varam pārbaudīt datus, izmantojot SELECT paziņojumu. Mēs saņemsim šādu izvadi:
PIVOT operators
Šis operators tiek izmantots, lai pagrieztu tabulas vērtības izteiksmes. Tas pirmo reizi tika ieviests SQL Server 2005 versijā. Tas pārvērš datus no rindām uz kolonnām. Tas sadala unikālās vērtības no vienas kolonnas daudzās kolonnās un pēc tam apkopo atlikušās kolonnu vērtības, kas nepieciešamas gala rezultātā.
satur apakšvirkni java
Lai izveidotu PIVOT tabulu, ir jāveic šādas darbības:
- Atlasiet pamatdatu kopu pagriešanai.
- Izveidojiet pagaidu rezultātus, izmantojot atvasinātu tabulu vai CTE (kopēja tabulas izteiksme).
- Izmantojiet PIVOT operatoru.
Sintakse
Tālāk norādītā sintakse ilustrē PIVOT izmantošanu SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Ja mēs pārtraucam šo skriptu, mēs varam redzēt, ka tam ir divas atsevišķas sadaļas. Pirmajā sadaļā tiek atlasīti dati no galvenās tabulas, bet otrajā sadaļā tiek noteikts, kā tiks izveidota PIVOT tabula. Otrajā daļā ir arī daži īpaši atslēgvārdi, piemēram, SUM, FOR un IN. Apskatīsim šo atslēgvārdu nozīmi PIVOT operatorā.
SUMMA
Šis operators ir pieradis apkopo vērtības no norādītās kolonnas, kas jāizmanto PIVOT tabulā. Mums tas ir jāizmanto kopā ar operatoru PIVOT, lai iegūtu apkopoto kolonnu displejus vērtību sadaļām.
Atslēgvārdam
Šis atslēgvārds tiek izmantots PIVOT tabulas priekšrakstam norādiet PIVOT operatoru kurā kolonnā jāpiemēro PIVOT funkcija. Būtībā tas norāda kolonnu nosaukumus, kas tiks pārveidoti no rindām uz kolonnām.
IN Atslēgvārds
Šis atslēgvārds uzskaita visas unikālās vērtības no PIVOT kolonnas, lai tās tiktu parādītas kā PIVOT tabulas kolonnas.
Piemērs
Ļaujiet mums to saprast ar dažādu piemēru palīdzību.
1. Tālāk sniegtajā paziņojumā vispirms tiek atlasīta kolonna Gads, Ziemeļi un Dienvidi kā pamatdati pagriešanai. Pēc tam izveidojiet pagaidu rezultātu, izmantojot atvasināto tabulu, un visbeidzot izmantojiet PIVOT operatoru, lai ģenerētu galīgo izvadi. Šī produkcija tiek pasūtīta arī augošā gadā.
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
Izpildot šo paziņojumu, tiks parādīta zemāk redzamā izvade. Šeit mēs varam redzēt aprēķinātā Ziemeļu un Dienvidu reģionu pārdošanas apjoma summa, kas atbilst gada vērtībām .
2. Šis ir vēl viens piemērs, kurā mēs aprēķināsim katra gada pārdošanas apjomu, kas atbilst reģiona vērtībām:
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;
Izpildot šo paziņojumu, tiks radīt kļūdu jo mēs nevaram tieši norādīt skaitlisko vērtību kā kolonnas nosaukumu.
Tomēr SQL Server ļauj mums izvairīties no šīs problēmas, izmantojot iekavas pirms katras vesela skaitļa vērtības. Atjauninātais paziņojums ir parādīts šajā koda fragmentā:
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;
Šis paziņojums ir veiksmīgi izpildīts un parāda aprēķināto pārdošanas apjomu katram gadam atbilstoši reģiona vērtībām:
3. Iepriekšējais PIVOT tabulas iegūšanas piemērs ir noderīgs, ja mēs zinām visas iespējamās PIVOT kolonnas vērtības. Bet pieņemsim, ka nākamajā gadā kolonnu skaits tiks palielināts. Ņemot vērā iepriekšējo piemēru, mums ir 2010., 2011. un 2012. gads kā PIVOT kolonnas. Tomēr nav garantijas, ka šīs kolonnas nākotnē nemainīsies. Kas notiek, ja mums ir dati no 2013. vai 2014. gada, vai varbūt pat vairāk? Šādos gadījumos mums būs jāizmanto dinamiskā PIVOT tabula vaicājumus, lai novērstu šo problēmu.
Dinamiskais PIVOT tabulas vaicājums iekapsulē visu PIVOT skriptu saglabātā procedūrā. Šī procedūra nodrošinās regulējamas iespējas, ļaujot mums mainīt mūsu prasības, mainot dažas parametrizētās vērtības.
kādi mēneši ir Q3
Šis SQL kods izskaidro dinamiskās PIVOT tabulas darbību. Šajā skriptā mēs vispirms esam izguvuši visas atšķirīgās vērtības no PIVOT kolonnas un pēc tam uzrakstījām SQL priekšrakstu izpildei ar PIVOT vaicājumu izpildes laikā. Apskatīsim izvadi pēc šī skripta izpildes:
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
Šajā skriptā mēs esam izveidojuši divus parametrizētus mainīgos. Tās apraksts ir sniegts zemāk:
@PivotColumn : šis mainīgais iegūs kolonnas nosaukumu no sākotnējās tabulas, kurā ir izveidota PIVOT tabula. Piemēram , šeit slejā “Reģions” tiek parādīti visi kolonnās pieejamie reģioni.
@PivotList : šis mainīgais izmantos kolonnu sarakstu, kuru vēlamies parādīt kā izvades kolonnu PIVOT tabulā.
Dinamiskās saglabātās procedūras izpilde
Pēc veiksmīgas dinamiskās saglabātās procedūras izveides esam gatavi to izpildīt. Šis paziņojums tiek izmantots, lai izsauktu dinamisko saglabāto procedūru, lai parādītu PIVOT tabulu izpildes laikā:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Šeit mēs tagad esam norādījuši kolonnas nosaukumu ' Novads ' kā pirmo parametru un PIVOT kolonnu sarakstu kā otro parametru. Izpildot skriptu, tiks parādīta šāda izvade:
Tagad izpildlaikā mēs varam pievienot vairāk kolonnu, lai parādītu PIVOT tabulu, kas nav iespējams ar pirmajiem diviem piemēriem.
UNPIVOT operators
Tā ir PIVOT operatora apgrieztā metode SQL Server. Tas mans šis operators veic pretēja PIVOT darbība pārvēršot datus no kolonnām rindās. UNPIVOT operators arī pagriež PIVOT tabulu parastajā tabulā. Tas pirmo reizi tika ieviests SQL Server 2005 versijā.
Sintakse
struct masīva c programmēšana
Tālāk norādītā sintakse ilustrē UNPIVOT SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Piemērs
Ļaujiet mums saprast, kā UNPIVOT PIVOT darbību, izmantojot piemērus. Vispirms mēs izveidosim oriģinālā tabula un PIVOT tabula un pēc tam šajā tabulā lietoja UNPIVOT operatoru.
Šis koda fragments vispirms deklarē pagaidu tabulas mainīgo @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Tālāk mēs ievietosim vērtības šajā tabulā, kā norādīts tālāk:
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
Tagad mēs varam veikt UNPIVOT darbību, izmantojot šādu paziņojumu:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Koda fragmenta izpilde atgriezīs šādu izvadi:
Tālāk sniegtais koda fragments ir vēl viens piemērs, kas vispirms veic PIVOT darbību un pēc tam UNPIVOT darbību tajā pašā tabulā vienā vaicājumā:
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
Izpildot koda fragmentu, tiks parādīta tā pati izvade:
PIEZĪME. UNPIVOT process ir PIVOT procedūras apgrieztā darbība, taču tā nav precīza apvērse. Tā kā rindas ir sapludinātas, kad PIVOT aprēķina kopsavilkumu, un rezultātā daudzas rindas ir apvienotas vienā rindā, tāpēc UNPIVOT darbība nevar padarīt tabulu tādu pašu, kā oriģināls. Tomēr, ja PIVOT operators nesapludina daudzas rindas vienā rindā, tad UNPIVOT operators var iegūt sākotnējo tabulu no PIVOT izvades.
Secinājums
Šis raksts sniegs pilnu pārskatu par PIVOT un UNPIVOT operatoriem SQL Server un konvertēs tabulas izteiksmi citā. Nekad nevajadzētu aizmirst, ka UNPIVOT ir PIVOT apgrieztā darbība, taču tā nav precīza PIVOT rezultāta apgrieztā vērtība.