Ta članek bo podal popoln pregled uporabe operaterjev PIVOT in UNPIVOT v strežniku SQL Server. Operatorja PIVOT in UNPIVOT sta podobna relacijskim operaterjem, ki omogočajo preoblikovanje izraza z vrednostjo tabele v drugo tabelo . Oba operaterja ustvarjata večdimenzionalno poročanje, ki pomaga hitro kombinirati in primerjati veliko količino podatkov.
Lahko uporabimo Operater PIVOT ko moramo transformirati tabelarne izraze. Razcepi enolične vrednosti iz enega stolpca v več stolpcev v končnem rezultatu. Tudi agregati preostale vrednosti stolpcev, zahtevane v končnem rezultatu. Operater UNPIVOT pretvori podatke iz stolpcev izraza z vrednostjo tabele v vrednosti stolpcev, kar je obratno od PIVOT.
Naj ga razumemo s pomočjo spodnjega preprostega diagrama:
prekiniti javo
Na levi strani te slike lahko vidimo izvirni nabor podatkov , ki ima tri stolpce: leto, regija, in Prodaja . Nato lahko vidimo tabelo PIVOT na desni strani, ki je sestavljena z vrtenjem Regija (vrstice) na sever in jug (stolpci) . Po pretvorbi vrstic v stolpce lahko naredimo skupek vrednosti stolpca Prodaja za vsako presečišče med stolpci in vrsticami vrtilne tabele.
Najprej ustvarimo tabelo z imenom pivot_demo za prikaz operatorjev PIVOT in UNPIVOT. Naslednji stavek ustvari novo tabelo v naši podani bazi podatkov:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Nato v to tabelo vstavite nekaj podatkov, kot je prikazano spodaj:
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);
Podatke lahko preverimo s stavkom SELECT. Dobili bomo spodnji rezultat:
Operater PIVOT
Ta operator se uporablja za zasuk izrazov s tabelnimi vrednostmi. Prvič je bil predstavljen v različici SQL Server 2005. Pretvarja podatke iz vrstic v stolpce. Edinstvene vrednosti iz enega stolpca razdeli na številne stolpce in nato združi preostale vrednosti stolpcev, zahtevane v končnem rezultatu.
Za ustvarjanje vrtilne tabele moramo slediti naslednjim korakom:
- Izberite osnovni nabor podatkov za vrtenje.
- Ustvarite začasne rezultate s pomočjo izpeljane tabele ali CTE (skupni tabelni izraz).
- Uporabite operater PIVOT.
Sintaksa
Naslednja sintaksa ponazarja uporabo PIVOT v SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Če ta skript zlomimo, lahko vidimo, da ima dva ločena dela. Prvi razdelek izbere podatke iz glavne tabele, drugi razdelek pa določa, kako bo sestavljena VRTILJNA tabela. Drugi del vsebuje tudi nekaj posebnih ključnih besed, kot so SUM, FOR in IN. Poglejmo si pomen teh ključnih besed v operatorju PIVOT.
SUM
Ta operater je vajen združiti vrednosti iz podanega stolpca, ki bo uporabljen v VRTLJIVI tabeli. Uporabiti ga moramo z operaterjem PIVOT, da dobimo prikaze združenih stolpcev za odseke vrednosti.
ZA ključno besedo
Ta ključna beseda se uporablja za stavek PIVOT tabele naroči operaterju PIVOT na katerem stolpcu naj se uporabi funkcija PIVOT. V bistvu označuje imena stolpcev, ki se bodo preoblikovala iz vrstic v stolpce.
IN Ključna beseda
Ta ključna beseda navede vse edinstvene vrednosti iz stolpca PIVOT, da bodo prikazani kot stolpci tabele PIVOT.
Primer
Razumejmo ga s pomočjo različnih primerov.
1. Naslednji stavek najprej izbere stolpec Leto, Sever in Jug kot osnovne podatke za vrtenje. Nato ustvarite začasni rezultat z uporabo izpeljane tabele in na koncu uporabite operater PIVOT, da ustvarite končni rezultat. Ta izhod je urejen tudi v naraščajočem letu.
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
Izvedba tega stavka bo ustvarila spodnji rezultat. Tukaj lahko vidimo, izračunana vsota prodaje severne in južne regije, ki ustreza letnim vrednostim .
2. To je še en primer, kjer bomo izračunali vsoto prodaje za vsako leto, ki ustreza vrednostim regije:
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;
Izvedba te izjave bo povzroči napako ker ne moremo neposredno določiti številčne vrednosti kot ime stolpca.
Vendar nam SQL Server omogoča, da se izognemo tej težavi z uporabo oklepajev pred vsako vrednostjo celega števila. Posodobljena izjava je prikazana v naslednjem delčku kode:
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;
Ta izjava je bila uspešno izvedena in prikazuje izračunano vsoto prodaje za vsako leto, ki ustreza vrednostim regije:
3. Prejšnji primer pridobivanja tabele PIVOT je koristen, ko poznamo vse možne vrednosti stolpca PIVOT. Recimo, da se v prihajajočem letu število stolpcev poveča. Če upoštevamo prejšnji primer, imamo leta 2010, 2011 in 2012 kot stolpce PIVOT. Vendar pa ni nobenega zagotovila, da se ti stolpci v prihodnosti ne bodo spremenili. Kaj se zgodi, če imamo podatke iz leta 2013 ali 2014 ali morda celo več? V takih primerih bomo morali uporabiti dinamična PIVOT tabela poizvedb za odpravo te težave.
Dinamična poizvedba tabele PIVOT zajema celoten skript PIVOT v shranjeni proceduri. Ta postopek bo zagotovil nastavljive možnosti, ki nam bodo omogočile spreminjanje naših zahtev s spremembo nekaj parametriranih vrednosti.
java tutorial za začetnike
Naslednja koda SQL pojasnjuje delovanje dinamične vrtilne tabele. V tem skriptu smo najprej pridobili vse različne vrednosti iz stolpca PIVOT in nato napisali stavek SQL za izvedbo s poizvedbo PIVOT med izvajanjem. Poglejmo izhod po izvedbi tega skripta:
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
V tem skriptu smo ustvarili dve parametrirani spremenljivki. Njegov opis je podan spodaj:
@Vrtilni stolpec : Ta spremenljivka bo prevzela ime stolpca iz izvirne tabele, na kateri je ustvarjena VRTLJIVA tabela. Na primer , tukaj stolpec 'Regija' prikazuje vse regije, ki so na voljo v stolpcih.
@PivotList : Ta spremenljivka bo vzela seznam stolpcev, ki jih želimo prikazati kot izhodni stolpec v tabeli PIVOT.
Izvajanje dinamične shranjene procedure
Po uspešni izdelavi dinamične shranjene procedure smo pripravljeni na njeno izvedbo. Naslednji stavek se uporablja za klic dinamične shranjene procedure za prikaz tabele PIVOT med izvajanjem:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Tukaj smo zdaj določili ime stolpca ' Regija ' kot prvi parameter in seznam stolpcev PIVOT kot drugi parameter. Izvajanje skripta bo prikazalo naslednje rezultate:
Zdaj lahko v prihodnosti med izvajanjem dodamo več stolpcev za prikaz VRTILJNE tabele, kar v prvih dveh primerih ni mogoče.
Operater UNPIVOT
Je obratna metoda operaterja PIVOT v strežniku SQL Server. Ta operater izvaja nasprotno delovanje PIVOT s pretvorbo podatkov iz stolpcev v vrstice. Operater UNPIVOT prav tako vrti tabelo PIVOT v običajno tabelo. Prvič je bil predstavljen v različici SQL Server 2005.
Sintaksa
Naslednja sintaksa ponazarja UNPIVOT v strežniku SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Primer
Dovolite nam, da s primeri razumemo, kako UNPIVOT operacijo PIVOT. Najprej bomo ustvarili originalna miza in PIVOT miza in nato uporabil operator UNPIVOT za to tabelo.
Naslednji delček kode najprej deklarira začasno spremenljivko tabele @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Nato bomo v to tabelo vstavili vrednosti, kot je prikazano spodaj:
mehurčkasto razvrščanje v Javi
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
Zdaj lahko izvedemo operacijo UNPIVOT z uporabo spodnje izjave:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Izvedba delčka kode bo vrnila naslednje rezultate:
Spodnji delček kode je še en primer za prvo izvedbo operacije PIVOT in nato operacije UNPIVOT na isti tabeli znotraj ene same poizvedbe:
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
Izvedba delčka kode bo prikazala enak rezultat:
OPOMBA: Postopek UNPIVOT je obratna operacija postopka PIVOT, vendar ni natančen obrat. Ker so bile vrstice spojene, ko je PIVOT izračunal agregat in združil številne vrstice v eno vrstico v rezultatu, zato operacija UNPIVOT ne more narediti tabele tako kot izvirnik. Če pa operater PIVOT ne združi več vrstic v eno samo vrstico, lahko operater UNPIVOT pridobi izvirno tabelo iz izhoda PIVOT.
Zaključek
Ta članek bo podal popoln pregled operaterjev PIVOT in UNPIVOT v strežniku SQL Server ter pretvoril izraz tabele v drugega. Nikoli ne smemo pozabiti, da je UNPIVOT obratna operacija PIVOT-a, ni pa ravno obratna operacija PIVOT-a.