Ovaj će članak dati potpuni pregled korištenja operatora PIVOT i UNPIVOT u SQL Serveru. Operatori PIVOT i UNPIVOT slični su relacijskim operatorima koji omogućuju transformiranje izraza s tabličnom vrijednošću u drugu tablicu . Oba operatera generiraju višedimenzionalna izvješća koja pomažu u brzom kombiniranju i usporedbi velike količine podataka.
Možemo koristiti PIVOT operator kada trebamo transformirati tablične izraze. Ono dijeli jedinstvene vrijednosti iz jednog stupca u više stupaca u konačnom rezultatu. Također agregati preostale vrijednosti stupca potrebne u konačnom rezultatu. UNPIVOT operator pretvara podatke iz stupaca izraza s tabličnom vrijednošću u vrijednosti stupca, što je obrnuto od PIVOT-a.
Razumimo to uz pomoć jednostavnog dijagrama danog u nastavku:
Na lijevoj strani ove slike možemo vidjeti izvorni skup podataka , koji ima tri stupca: godina, regija, i Prodajni . Zatim možemo vidjeti PIVOT tablicu na desnoj strani, koja je konstruirana rotiranjem Regija (retci) na sjever i jug (stupci) . Nakon pretvaranja redaka u stupce, možemo napraviti skup vrijednosti stupca Prodaja za svako sjecište između stupaca i redaka PIVOT tablice.
Kreirajmo prvo tablicu pod nazivom pivot_demo za demonstraciju operatora PIVOT i UNPIVOT. Sljedeća izjava stvara novu tablicu u našoj navedenoj bazi podataka:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Zatim umetnite neke podatke u ovu tablicu kao u nastavku:
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 možemo provjeriti pomoću naredbe SELECT. Dobit ćemo sljedeći izlaz:
PIVOT operater
Ovaj se operator koristi za rotiranje izraza s tabličnom vrijednošću. Prvi put je predstavljen u verziji SQL Server 2005. Pretvara podatke iz redaka u stupce. Dijeli jedinstvene vrijednosti iz jednog stupca u više stupaca, a zatim agregira preostale vrijednosti stupaca potrebne u konačnom rezultatu.
fibonaccijev niz u Javi
Za izradu PIVOT tablice moramo slijediti sljedeće korake:
- Odaberite osnovni skup podataka za okretanje.
- Stvorite privremene rezultate uz pomoć izvedene tablice ili CTE (zajednički tablični izraz).
- Iskoristite operator PIVOT.
Sintaksa
Sljedeća sintaksa ilustrira upotrebu PIVOT-a u SQL Serveru:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Ako razbijemo ovu skriptu, možemo vidjeti da ima dva odvojena odjeljka. Prvi odjeljak odabire podatke iz glavne tablice, a drugi odjeljak određuje kako će se konstruirati PIVOT tablica. Drugi dio također sadrži neke posebne ključne riječi kao što su SUM, FOR i IN. Pogledajmo značenje ovih ključnih riječi u PIVOT operatoru.
IZNOS
Ovaj operator je navikao agregirati vrijednosti iz navedenog stupca koji će se koristiti u PIVOT tablici. Moramo ga koristiti s operatorom PIVOT da bismo dobili agregirane prikaze stupaca za dijelove vrijednosti.
ZA ključnu riječ
Ova se ključna riječ koristi za naredbu PIVOT tablice uputiti operatera PIVOT-a na koji stupac treba primijeniti PIVOT funkciju. U osnovi, označava nazive stupaca koji će se transformirati iz redaka u stupce.
IN Ključna riječ
Ova ključna riječ navodi sve jedinstvene vrijednosti iz stupca PIVOT koji će se prikazati kao stupci tablice PIVOT.
Primjer
Shvatimo to uz pomoć raznih primjera.
1. Sljedeća izjava prvo odabire stupac Godina, Sjever i Jug kao osnovne podatke za okretanje. Zatim stvorite privremeni rezultat koristeći izvedenu tablicu i na kraju primijenite PIVOT operator za generiranje konačnog rezultata. Ovaj izlaz je također poredan u rastućoj godini.
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
Izvršenje ove naredbe proizvest će donji izlaz. Ovdje možemo vidjeti izračunati zbroj prodaje sjeverne i južne regije koji odgovara vrijednostima godine .
2. Ovo je još jedan primjer gdje ćemo izračunati zbroj prodaje za svaku godinu koja odgovara vrijednostima 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;
Izvršenje ove izjave će proizvesti grešku jer ne možemo izravno navesti numeričku vrijednost kao naziv stupca.
Međutim, SQL Server nam omogućuje da izbjegnemo ovaj problem korištenjem zagrada prije svake vrijednosti cijelog broja. Ažurirana izjava prikazana je u sljedećem isječku koda:
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;
Ova je izjava uspješno izvršena i prikazuje izračunati zbroj prodaje za svaku godinu koja odgovara vrijednostima regije:
3. Prethodni primjer dobivanja PIVOT tablice je koristan kada smo svjesni svih mogućih vrijednosti stupca PIVOT. Ali pretpostavimo da se u nadolazećoj godini broj stupaca poveća. Uzimajući u obzir prethodni primjer, imamo 2010., 2011. i 2012. godinu kao stupce PIVOT. Međutim, nema jamstva da se ti stupci neće promijeniti u budućnosti. Što se događa ako imamo podatke iz 2013. ili 2014., ili možda čak i više? U takvim slučajevima morat ćemo koristiti dinamička PIVOT tablica upita za rješavanje ovog problema.
Upit dinamičke PIVOT tablice enkapsulira cijelu PIVOT skriptu u pohranjenoj proceduri. Ovaj postupak će omogućiti podesive opcije, omogućujući nam da modificiramo naše zahtjeve promjenom nekoliko parametriziranih vrijednosti.
nit.uništiti
Sljedeći SQL kod objašnjava rad dinamičke PIVOT tablice. U ovoj skripti prvo smo dohvatili sve različite vrijednosti iz stupca PIVOT, a zatim napisali SQL naredbu za izvršavanje s upitom PIVOT tijekom izvođenja. Pogledajmo izlaz nakon izvršavanja ove skripte:
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
U ovoj skripti stvorili smo dvije parametrizirane varijable. Njegov opis je dat u nastavku:
@Zaokretni stupac : Ova varijabla će preuzeti naziv stupca iz originalne tablice na kojoj je stvorena PIVOT tablica. Na primjer , ovdje stupac 'Regija' prikazuje sve regije dostupne u stupcima.
@PivotList : Ova varijabla će uzeti popis stupaca koji želimo prikazati kao izlazni stupac u PIVOT tablici.
Izvršenje dinamičke pohranjene procedure
Nakon uspješne izrade dinamičke pohranjene procedure, spremni smo je izvršiti. Sljedeća izjava koristi se za pozivanje dinamičke pohranjene procedure za prikaz PIVOT tablice tijekom izvođenja:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Ovdje smo sada odredili naziv stupca ' Regija ' kao prvi parametar i popis stupaca PIVOT kao drugi parametar. Izvršavanje skripte prikazat će sljedeći izlaz:
Sada možemo dodati više stupaca u budućnosti tijekom izvođenja za prikaz PIVOT tablice, što nije moguće s prva dva primjera.
Operator UNPIVOT
To je obrnuta metoda operatora PIVOT u SQL Serveru. Ovaj operater obavlja suprotni rad od PIVOT-a pretvaranjem podataka iz stupaca u retke. Operator UNPIVOT također rotira PIVOT tablicu u običnu tablicu. Prvi put je predstavljen u verziji SQL Server 2005.
Sintaksa
pretvoriti niz u json objekt
Sljedeća sintaksa ilustrira UNPIVOT u SQL Serveru:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Primjer
Razmotrimo kako UNPIVOT operaciju PIVOT s primjerima. Prvo ćemo stvoriti originalna tablica i PIVOT tablica a zatim primijenio operator UNPIVOT na ovu tablicu.
Sljedeći isječak koda prvo deklarira privremenu varijablu tablice @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Zatim ćemo u ovu tablicu umetnuti vrijednosti kao u nastavku:
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
Sada možemo izvršiti operaciju UNPIVOT pomoću donje izjave:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Izvršenje isječka koda vratit će sljedeći izlaz:
Donji isječak koda još je jedan primjer za prvo izvođenje PIVOT operacije, a zatim UNPIVOT operacije na istoj tablici unutar jednog upita:
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
Izvršenje isječka koda prikazat će isti izlaz:
NAPOMENA: UNPIVOT proces je obrnuta operacija od PIVOT procedure, ali nije točan obrat. Budući da su retci bili spojeni kada je PIVOT izračunao agregat i kombinirao mnogo redaka u jedan redak u rezultatu, stoga operacija UNPIVOT ne može učiniti tablicu sličnom izvorniku. Međutim, ako operator PIVOT ne spoji mnogo redaka u jedan red, tada operator UNPIVOT može dobiti izvornu tablicu iz izlaza PIVOT.
Zaključak
Ovaj članak će dati potpuni pregled PIVOT i UNPIVOT operatora u SQL Serveru i pretvoriti tablični izraz u drugi. Nikada ne treba zaboraviti da je UNPIVOT operacija obrnuta od PIVOT-a, ali nije točna inverzna operacija PIVOT-a.