Tento článok poskytne úplný prehľad používania operátorov PIVOT a UNPIVOT na serveri SQL Server. Operátory PIVOT a UNPIVOT sú podobné relačným operátorom, ktoré umožňujú transformácia výrazu s hodnotou tabuľky na inú tabuľku . Obaja operátori vytvárajú multidimenzionálne správy, ktoré pomáhajú rýchlo kombinovať a porovnávať veľké množstvo údajov.
Môžeme použiť Operátor PIVOT keď potrebujeme transformovať výrazy s tabuľkovou hodnotou. Rozdeľuje sa jedinečné hodnoty z jedného stĺpca do mnohých stĺpcov v konečnom výsledku. To tiež agregátov zostávajúce hodnoty stĺpca požadované v konečnom výsledku. Operátor UNPIVOT konvertuje údaje zo stĺpcov výrazu s tabuľkovou hodnotou na hodnoty stĺpcov, čo je inverzná hodnota k PIVOT.
Poďme to pochopiť pomocou jednoduchého diagramu uvedeného nižšie:
Na ľavej strane tohto obrázku môžeme vidieť pôvodný súbor údajov , ktorý má tri stĺpce: rok, región, a Predaj . Ďalej vidíme na pravej strane PIVOT stôl, ktorý je konštruovaný otáčaním Región (riadky) na sever a juh (stĺpce) . Po prevedení riadkov na stĺpce môžeme urobiť súhrn hodnôt stĺpca Predaj pre každý priesečník medzi stĺpcami a riadkami PIVOT tabuľky.
matematická trieda java
Najprv vytvorte tabuľku s názvom pivot_demo demonštrovať operátory PIVOT a UNPIVOT. Nasledujúci príkaz vytvorí novú tabuľku v našej špecifikovanej databáze:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Potom do tejto tabuľky vložte niektoré údaje, ako je uvedené nižšie:
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);
Údaje môžeme overiť pomocou príkazu SELECT. Získame nasledujúci výstup:
Operátor PIVOT
Tento operátor sa používa na rotáciu výrazov s tabuľkovou hodnotou. Prvýkrát bol predstavený vo verzii SQL Server 2005. Prevádza údaje z riadkov do stĺpcov. Rozdeľuje jedinečné hodnoty z jedného stĺpca do mnohých stĺpcov a potom agreguje zostávajúce hodnoty stĺpcov požadované v konečnom výsledku.
Na vytvorenie PIVOT tabuľky musíme postupovať podľa nasledujúcich krokov:
- Vyberte základnú množinu údajov na pivotovanie.
- Vytvorte dočasné výsledky pomocou odvodenej tabuľky alebo CTE (bežný tabuľkový výraz).
- Využite operátor PIVOT.
Syntax
Nasledujúca syntax ilustruje použitie PIVOT v SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Ak tento skript porušíme, vidíme, že má dve samostatné sekcie. Prvá časť vyberá údaje z hlavnej tabuľky a druhá časť určuje, ako bude zostavená PIVOT tabuľka. Druhá časť obsahuje aj niektoré špeciálne kľúčové slová ako SUM, FOR a IN. Pozrime sa na význam týchto kľúčových slov v operátore PIVOT.
SUM
Tento operátor je zvyknutý agregovať hodnoty zo zadaného stĺpca, ktorý sa má použiť v PIVOT tabuľke. Musíme ho použiť s operátorom PIVOT, aby sme získali agregované zobrazenie stĺpcov pre sekcie hodnôt.
PRE Kľúčové slovo
Toto kľúčové slovo sa používa pre príkaz tabuľky PIVOT informujte operátora PIVOT na ktorý stĺpec sa má použiť funkcia PIVOT. V podstate označuje názvy stĺpcov, ktoré sa budú transformovať z riadkov na stĺpce.
IN Kľúčové slovo
Toto kľúčové slovo uvádza všetky jedinečné hodnoty zo stĺpca PIVOT, aby sa zobrazili ako stĺpce PIVOT tabuľky.
Príklad
Poďme to pochopiť pomocou rôznych príkladov.
importovať skener java
1. Nasledujúce vyhlásenie najprv vyberie stĺpce Rok, Sever a Juh ako základné údaje na otáčanie. Potom vytvorte dočasný výsledok pomocou odvodenej tabuľky a nakoniec použite operátor PIVOT na vygenerovanie konečného výstupu. Tento výstup je tiež usporiadaný vo vzostupnom roku.
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
Vykonaním tohto príkazu sa vytvorí výstup uvedený nižšie. Tu môžeme vidieť vypočítaný súčet tržieb regiónu Sever a Juh zodpovedajúci ročným hodnotám .
2. Toto je ďalší príklad, v ktorom vypočítame súčet predajov za každý rok zodpovedajúci hodnotám regiónu:
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;
Vykonanie tohto príkazu bude spôsobiť chybu pretože nemôžeme zadať číselnú hodnotu ako názov stĺpca priamo.
SQL Server nám však umožňuje vyhnúť sa tomuto problému použitím hranatých zátvoriek pred každou celočíselnou hodnotou. Aktualizované vyhlásenie je zobrazené v nasledujúcom útržku kódu:
bajtov na reťazec python
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;
Toto vyhlásenie sa úspešne vykonalo a zobrazuje vypočítanú sumu predaja za každý rok zodpovedajúcu hodnotám regiónu:
3. Predchádzajúci príklad získania PIVOT tabuľky je užitočný, keď poznáme všetky možné hodnoty stĺpca PIVOT. Predpokladajme však, že v nasledujúcom roku sa počet stĺpcov zvýši. Vzhľadom na predchádzajúci príklad máme ako stĺpce PIVOT roky 2010, 2011 a 2012. Neexistuje však žiadna záruka, že tieto stĺpce sa v budúcnosti nezmenia. Čo sa stane, ak budeme mať údaje z roku 2013 alebo 2014 alebo možno ešte viac? V takýchto prípadoch budeme musieť použiť dynamická PIVOT tabuľka otázky na vyriešenie tohto problému.
Dotaz dynamickej tabuľky PIVOT zapuzdrí celý skript PIVOT do uloženej procedúry. Tento postup poskytne nastaviteľné možnosti, čo nám umožní upraviť naše požiadavky zmenou niekoľkých parametrizovaných hodnôt.
Nasledujúci kód SQL vysvetľuje fungovanie dynamickej PIVOT tabuľky. V tomto skripte sme najprv získali všetky odlišné hodnoty zo stĺpca PIVOT a potom napísali príkaz SQL na vykonanie s dotazom PIVOT v čase spustenia. Pozrime sa na výstup po vykonaní tohto skriptu:
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 tomto skripte sme vytvorili dve parametrizované premenné. Jeho popis je uvedený nižšie:
@PivotColumn : Táto premenná prevezme názov stĺpca z pôvodnej tabuľky, na ktorej je vytvorená PIVOT tabuľka. Napríklad , tu stĺpec „Región“ zobrazuje všetky regióny dostupné v stĺpcoch.
@PivotList : Táto premenná prevezme zoznam stĺpcov, ktorý chceme zobraziť ako výstupný stĺpec v PIVOT tabuľke.
Vykonanie dynamickej uloženej procedúry
Po úspešnom vytvorení dynamickej uloženej procedúry sme pripravení ju spustiť. Nasledujúci príkaz sa používa na volanie dynamickej uloženej procedúry na zobrazenie tabuľky PIVOT v čase spustenia:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Tu sme teraz zadali názov stĺpca ' región ' ako prvý parameter a zoznam stĺpcov PIVOT ako druhý parameter. Po spustení skriptu sa zobrazí nasledujúci výstup:
Teraz môžeme v budúcnosti za behu pridať ďalšie stĺpce, aby sa zobrazila PIVOT tabuľka, čo pri prvých dvoch príkladoch nie je možné.
Operátor UNPIVOT
Je to reverzná metóda operátora PIVOT v SQL Server. To mans tento operátor vykonáva opačná prevádzka PIVOT prevodom údajov zo stĺpcov na riadky. Operátor UNPIVOT tiež otáča PIVOT tabuľku na bežnú tabuľku. Prvýkrát bol predstavený vo verzii SQL Server 2005.
celé číslo v porovnaní s javou
Syntax
Nasledujúca syntax ilustruje UNPIVOT v SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Príklad
Poďme pochopiť, ako UNPIVOTE operáciu PIVOT pomocou príkladov. Najprv vytvoríme originálny stôl a PIVOT stôl a potom na túto tabuľku aplikoval operátor UNPIVOT.
Nasledujúci útržok kódu najskôr deklaruje dočasnú premennú tabuľky @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Ďalej do tejto tabuľky vložíme hodnoty, ako je uvedené nižšie:
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
Teraz môžeme vykonať operáciu UNPIVOT pomocou nižšie uvedeného príkazu:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Spustenie útržku kódu vráti nasledujúci výstup:
Nižšie uvedený útržok kódu je ďalším príkladom, ktorý najskôr vykoná operáciu PIVOT a potom operáciu UNPIVOT na tej istej tabuľke v rámci jedného dotazu:
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
Po vykonaní útržku kódu sa zobrazí rovnaký výstup:
POZNÁMKA: Proces UNPIVOT je obrátená operácia postupu PIVOT, ale nie je to presný obrat. Keďže riadky boli zlúčené, keď PIVOT vypočítava súhrn a kombinuje veľa riadkov do jedného riadka vo výsledku, operácia UNPIVOT nemôže vytvoriť tabuľku ako originál. Ak však operátor PIVOT nezlúči veľa riadkov do jedného riadka, operátor UNPIVOT môže získať pôvodnú tabuľku z výstupu PIVOT.
Záver
Tento článok poskytne úplný prehľad operátorov PIVOT a UNPIVOT na serveri SQL Server a prevedie tabuľkový výraz na iný. Nikdy by sa nemalo zabúdať, že UNPIVOT je obrátená operácia PIVOT, ale nie je to presná inverzná hodnota výsledku PIVOT.