Tento článek poskytne úplný přehled používání operátorů PIVOT a UNPIVOT na serveru SQL Server. Operátory PIVOT a UNPIVOT jsou podobné relačním operátorům, které umožňují transformace výrazu s hodnotou tabulky na jinou tabulku . Oba operátoři generují vícerozměrné reporty, které pomáhají rychle kombinovat a porovnávat velké množství dat.
Můžeme použít Operátor PIVOT když potřebujeme transformovat výrazy s tabulkovou hodnotou. To rozděluje jedinečné hodnoty z jednoho sloupce do mnoha sloupců v konečném výsledku. To také agregáty zbývající hodnoty sloupce požadované v konečném výsledku. Operátor UNPIVOT převede data ze sloupců výrazu s tabulkovou hodnotou na hodnoty sloupců, což je opak PIVOT.
Pojďme to pochopit pomocí jednoduchého schématu uvedeného níže:
Na levé straně tohoto obrázku můžeme vidět původní datový soubor , který má tři sloupce: rok, region, a Odbyt . Dále můžeme vidět PIVOT stůl na pravé straně, který je konstruován otáčením Region (řádky) na sever a jih (sloupce) . Po převedení řádků na sloupce můžeme provést souhrn hodnot sloupce Prodej pro každý průsečík mezi sloupci a řádky PIVOT tabulky.
Nejprve vytvoříme tabulku s názvem pivot_demo demonstrovat operátory PIVOT a UNPIVOT. Následující příkaz vytvoří novou tabulku v naší zadané databázi:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Dále do této tabulky vložte některá data, jak je uvedeno níže:
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);
Data můžeme ověřit pomocí příkazu SELECT. Získáme následující výstup:
Operátor PIVOT
Tento operátor se používá k otáčení výrazů s tabulkovou hodnotou. Poprvé byl představen ve verzi SQL Server 2005. Převádí data z řádků na sloupce. Rozdělí jedinečné hodnoty z jednoho sloupce do mnoha sloupců a poté agreguje zbývající hodnoty sloupců požadované v konečném výsledku.
K vytvoření PIVOT tabulky musíme postupovat podle následujících kroků:
- Vyberte základní datovou sadu pro pivotování.
- Vytvářejte dočasné výsledky pomocí odvozené tabulky nebo CTE (common table expression).
- Využijte operátor PIVOT.
Syntax
Následující syntaxe ilustruje použití PIVOT v SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Pokud tento skript porušíme, uvidíme, že má dvě samostatné sekce. První část vybírá data z hlavní tabulky a druhá část určuje, jak bude konstruována PIVOT tabulka. Druhá část také obsahuje některá speciální klíčová slova jako SUM, FOR a IN. Podívejme se na význam těchto klíčových slov v operátoru PIVOT.
SOUČET
Tento operátor je zvyklý agregovat hodnoty ze zadaného sloupce, který má být použit v PIVOT tabulce. Musíme jej použít s operátorem PIVOT, abychom získali zobrazení agregovaných sloupců pro sekce hodnot.
PRO Klíčové slovo
Toto klíčové slovo se používá pro příkaz PIVOT table to instruovat operátora PIVOT na který sloupec má být funkce PIVOT aplikována. V zásadě označuje názvy sloupců, které se budou transformovat z řádků na sloupce.
Klíčové slovo IN
Toto klíčové slovo uvádí všechny jedinečné hodnoty ze sloupce PIVOT se zobrazí jako sloupce PIVOT tabulky.
Příklad
Pojďme to pochopit pomocí různých příkladů.
1. Následující příkaz nejprve vybere sloupce Rok, Sever a Jih jako základní data pro pivotování. Poté vytvořte dočasný výsledek pomocí odvozené tabulky a nakonec použijte operátor PIVOT pro vygenerování konečného výstupu. Tento výstup je také řazen ve vzestupném roce.
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
Provedení tohoto příkazu vytvoří níže uvedený výstup. Zde můžeme vidět vypočtený součet tržeb severního a jižního regionu odpovídající ročním hodnotám .
2. Toto je další příklad, kde budeme vypočítat součet prodejů za každý rok odpovídající hodnotám regionu:
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;
Provedení tohoto příkazu bude způsobit chybu protože nemůžeme zadat číselnou hodnotu jako název sloupce přímo.
pole řetězců
SQL Server nám však umožňuje vyhnout se tomuto problému pomocí závorek před každou celočíselnou hodnotou. Aktualizovaný příkaz je zobrazen v následujícím fragmentu kódu:
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;
Tento výpis byl úspěšně proveden a zobrazuje vypočítaný součet prodejů za každý rok odpovídající hodnotám regionu:
3. Předchozí příklad získání PIVOT tabulky je užitečný, když známe všechny možné hodnoty sloupců PIVOT. Předpokládejme však, že v nadcházejícím roce se počet sloupců zvýší. Vzhledem k předchozímu příkladu máme jako sloupce PIVOT roky 2010, 2011 a 2012. Neexistuje však žádná záruka, že se tyto sloupce v budoucnu nezmění. Co se stane, když máme data z roku 2013 nebo 2014, nebo možná ještě více? V takových případech budeme muset použít dynamická PIVOT tabulka dotazy k vyřešení tohoto problému.
Dynamický dotaz PIVOT tabulky zapouzdří celý skript PIVOT do uložené procedury. Tento postup poskytne nastavitelné možnosti, což nám umožní upravit naše požadavky změnou několika parametrizovaných hodnot.
Následující kód SQL vysvětluje fungování dynamické PIVOT tabulky. V tomto skriptu jsme nejprve načetli všechny odlišné hodnoty ze sloupce PIVOT a poté zapsali příkaz SQL pro provedení s dotazem PIVOT za běhu. Podívejme se na výstup po provedení tohoto 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 skriptu jsme vytvořili dvě parametrizované proměnné. Jeho popis je uveden níže:
@PivotColumn : Tato proměnná převezme název sloupce z původní tabulky, ve které je vytvořena PIVOT tabulka. Například , zde sloupec 'Region' zobrazuje všechny oblasti dostupné ve sloupcích.
@PivotList : Tato proměnná převezme seznam sloupců, který chceme zobrazit jako výstupní sloupec v PIVOT tabulce.
Provedení dynamické uložené procedury
Po úspěšném vytvoření dynamické uložené procedury jsme připraveni ji spustit. Následující příkaz se používá k volání dynamické uložené procedury k zobrazení PIVOT tabulky za běhu:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Zde jsme nyní zadali název sloupce ' Kraj ' jako první parametr a seznam sloupců PIVOT jako druhý parametr. Spuštěním skriptu se zobrazí následující výstup:
Nyní můžeme v budoucnu za běhu přidávat další sloupce pro zobrazení PIVOT tabulky, což u prvních dvou příkladů není možné.
Operátor UNPIVOT
Je to obrácená metoda operátoru PIVOT v SQL Server. To mans tento operátor provádí opačný provoz PIVOT převodem dat ze sloupců na řádky. Operátor UNPIVOT také otočí PIVOT tabulku do běžné tabulky. Poprvé byl představen ve verzi SQL Server 2005.
Syntax
Následující syntaxe 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)
Příklad
Pojďme si na příkladech porozumět, jak ROZDĚLIT operaci PIVOT. Nejprve vytvoříme originální stůl a PIVOT stůl a poté na tuto tabulku aplikoval operátor UNPIVOT.
Následující fragment kódu nejprve deklaruje dočasnou proměnnou tabulky @Tab:
css první dítě
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Dále do této tabulky vložíme hodnoty, jak je uvedeno níže:
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
Nyní můžeme provést operaci UNPIVOT pomocí níže uvedeného příkazu:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Spuštění fragmentu kódu vrátí následující výstup:
Níže uvedený fragment kódu je dalším příkladem, který nejprve provede operaci PIVOT a poté operaci UNPIVOT na stejné tabulce v rámci jednoho 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
Spuštěním úryvku kódu se zobrazí stejný výstup:
POZNÁMKA: Proces UNPIVOT je obrácená operace postupu PIVOT, ale nejedná se o přesné obrácení. Protože byly řádky sloučeny, když PIVOT vypočítává agregaci a zkombinuje mnoho řádků do jednoho řádku ve výsledku, operace UNPIVOT nemůže vytvořit tabulku jako originál. Pokud však operátor PIVOT nesloučí mnoho řádků do jednoho řádku, může operátor UNPIVOT získat původní tabulku z výstupu PIVOT.
Závěr
Tento článek poskytne úplný přehled operátorů PIVOT a UNPIVOT na serveru SQL Server a převede tabulkový výraz na jiný. Nikdy by se nemělo zapomínat, že UNPIVOT je obrácená operace PIVOT, ale není to přesná inverze výsledku PIVOT.