SQL Pivot - umíte převádět řádky na sloupce

Relační ukládat obrovské množství dat ve formě tabulek. Tyto tabulky mohou mít libovolný počet řádků a sloupců. Ale co kdybyste museli změnit data na úrovni řádků na sloupcová? V tomto článku o SQL Pivot vám ukážu, jak můžete převést řádky na sloupec na serveru SQL.

V tomto článku se budeme zabývat následujícími tématy:

• Co je PIVOT v SQL?
• Syntax
• Příklad
• Fungování klauzule PIVOT
• SQL UNPIVOT

Co je PIVOT v SQL?

PIVOT se používá k otočení hodnoty tabulky převedením jedinečných hodnot jednoho sloupce na více sloupců. Používá se k otočení řádků na hodnoty sloupců a v případě potřeby spustí agregace zbývajících hodnot sloupce.

UNPIVOT se naproti tomu používá k provádění opačných operací. Používá se tedy k převodu sloupců konkrétní tabulky na hodnoty sloupců.

Pokud v tomto článku pokračujeme, pochopíme syntaxi SQL Pivot.

Syntax:

VYBERTE NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnName FROM (SELECT query which produce the data) AS [alias pro počáteční dotaz] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName sloupce, jehož hodnoty se stanou hlavičkami sloupců] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last otočený sloupec])) AS [alias pro kontingenční tabulku]

Tady,můžete také použít Klauzule ORDER BY seřadit hodnoty vzestupně nebo sestupně. Nyní, když víte, co je PIVOT v SQL a jeho základní syntaxe, pojďme se posunout vpřed a uvidíme, jak jej používat.

Příklady

Pro vaše lepší pochopení zvážím následující tabulku, abych vám vysvětlil všechny příklady.

Tabulka dodavatele:

Napíšeme jednoduchý dotaz, abychom načetli průměrné náklady vynaložené každým zákazníkem.

VYBERTE IDZákazníka, AVG (náklady) JAKO PRŮMĚRNÝCENOZákazníka OD SKUPINY dodavatelů PODLE IDZákazníka

Výstup:

Nyní řekněme, že chceme otočit výše uvedenou tabulku. Zde se hodnoty sloupce CustomerID stanou záhlavími sloupců.

- Vytvořte kontingenční tabulku s jedním řádkem a třemi sloupci VYBERTE 'PrůměrnéCostofCustomer' JAKO Cost_According_To_Customers, [11], [22], [33] Z (VYBERTE ID zákazníka, Náklady Z Dodavatelů) JAKO SourceTable PIVOT (AVG (Náklady) ZA ID zákazníka v ( [11], [22], [33])) JAKO kontingenční tabulka

Výstup:

Poznámka: Když používáte agregační funkce s PIVOTem nebudou při výpočtu agregace brány v úvahu hodnoty null.

To byl základní příklad, ale pojďme nyní pochopit, jak fungovala klauzule PIVOT.

Fungování klauzule PIVOT

Jak můžete výše odkázat, při vytváření PIVOT TABLE musíte postupovat podle následujících kroků:

• Vyberte sloupce pro otočení
• Poté vyberte zdrojovou tabulku.
• Použijte operátor PIVOT a poté použijte agregační funkce.
• Uveďte kontingenční hodnoty.

Vyberte sloupce pro otočení

Zpočátku musíme určit pole, která mají být zahrnuta do našich výsledků. V našem příkladu jsem považoval sloupec AverageCostofCustomer v kontingenční tabulce. Poté jsme vytvořili další tři sloupce se záhlavími sloupců 11, 22 a 33. Příklad -

VYBERTE 'PrůměrnýCoofofonik' JAKO Cost_According_To_Customers, [11], [22], [33]

Vyberte zdrojovou tabulku

Dále musíte zadat příkaz SELECT, který vrátí zdrojová data kontingenční tabulky. V našem příkladu vracíme CustomerID a náklady z tabulky Dodavatelé.

(VYBERTE IDZákazníka, náklady od dodavatelů) JAKO Zdrojová tabulka

Použijte operátor PIVOT a poté použijte agregační funkce

Dále musíte určit agregační funkci, která se má použít při vytváření kontingenční tabulky. V našem příkladu jsem k výpočtu průměrných nákladů použil funkci AVG.

PIVOT (AVG (cena)

Uveďte kontingenční hodnoty

Nakonec musíte zmínit hodnoty, které musí být zahrnuty do výsledné kontingenční tabulky. Tyto hodnoty budou použity jako záhlaví sloupců v kontingenční tabulce.

PRO ID zákazníka ([11], [22], [33])) JAKO kontingenční tabulka

Tak fungují operátoři PIVOT. Pokračujeme v tomto článku na SQL PIVOT, pojďme pochopit, jak se liší od SQL UNPIVOT.

SQL UNPIVOT

Operátor SQL UNPIVOT se používá k provedení opačné operace než operace PIVOT. Používá se k otočení dat sloupce na data na úrovni řádků. Syntaxe UNPIVOT je podobná syntaxi PIVOT. Jediný rozdíl je v tom, že musíte použít ' UNPIVOT ” .

Příklad:

Vytvořme tabulku se sloupci SupplierID, AAA, BBB a CCC. Také vložte několik hodnot.

CREATE TABLE sampletable (SupplierID int, AAA int, BBB int, CCC int) GO INSERT INTO sampletable VALUES (1,3,5,6) INSERT INTO sampletable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) GO

Výstup:

Nyní řekněme, že chceme otočit stůl. Chcete-li to provést, můžete odkázat na následující kód:

VYBERTE ID dodavatele, zákazníky, produkty OD (VYBRAT dodavatel, AAA, BBB, CCC OD vzorkovatelné) p UNPIVOT (produkty PRO ZÁKAZNÍKY V (AAA, BBB, CCC)) JAKO příklad GO

Takto můžete použít SQL PIVOT a UNPIVOT. Tímto skončíme tento článek. Doufám, že jste pochopili, jak používat SQL. Pokud se chcete dozvědět více o MySQL a seznamte se s touto relační databází open-source, podívejte se na naši který přichází s živým tréninkem vedeným instruktorem a zkušenostmi z reálného projektu. Toto školení vám pomůže porozumět MySQL do hloubky a pomůže vám dosáhnout zvládnutí předmětu.

Máte na nás dotaz? Uveďte to prosím v sekci komentářů tohoto článku o SQL Pivot a ozvu se vám.