SQL Pivot - vedieť, ako prevádzať riadky na stĺpce

Vzťahové ukladať obrovské množstvo údajov vo forme tabuliek. Tieto tabuľky môžu mať ľubovoľný počet riadkov a stĺpcov. Čo by sa však stalo, keby ste museli zmeniť údaje na úrovni riadkov na stĺpcové? V tomto článku o SQL Pivote vám ukážem, ako môžete prevádzať riadky na stĺpce na serveri SQL Server.

V tomto článku sa budeme venovať nasledujúcim témam:

• Čo je PIVOT v SQL?
• Syntax
• Príklad
• Fungovanie klauzuly PIVOT
• SQL UNPIVOT

Čo je PIVOT v SQL?

PIVOT sa používa na otáčanie tabulkovej hodnoty prevádzaním jedinečných hodnôt jedného stĺpca do viacerých stĺpcov. Používa sa na rotáciu riadkov na hodnoty stĺpcov a v prípade potreby spúšťa agregácie so zvyšnými hodnotami stĺpca.

UNPIVOT sa na druhej strane používa na vykonávanie opačných operácií. Používa sa teda na prevod stĺpcov konkrétnej tabuľky na hodnoty stĺpcov.

Ak v tomto článku pokračujeme ďalej, pochopme syntax produktu SQL Pivot.

Syntax:

SELECT 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 pre počiatočný dopyt] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName stĺpca, ktorého hodnoty sa stanú hlavičkami stĺpcov] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last otočený stĺpec])) AS [alias pre kontingenčnú tabuľku]

Tu,môžete tiež použiť Klauzula ORDER BY hodnoty zoradiť vzostupne alebo zostupne. Teraz, keď viete, čo je PIVOT v jazyku SQL a jeho základná syntax, posuňme sa ďalej a pozrime sa, ako ho používať.

Príklady

Pre vaše lepšie pochopenie zvážim nasledujúcu tabuľku, ktorá vám vysvetlí všetky príklady.

Tabuľka dodávateľov:

Poďme napísať jednoduchý dotaz, aby sme zistili priemerné náklady, ktoré každý zákazník strávil.

VYBERTE ID zákazníka, AVG (náklady) AKO Priemerný zákazník zo skupiny dodávateľov PODĽA ID zákazníka

Výkon:

Teraz povedzme, že chceme otočiť vyššie uvedenú tabuľku. Tu sa hodnoty stĺpcov CustomerID stanú hlavičkami stĺpcov.

- Vytvorte kontingenčnú tabuľku s jedným riadkom a tromi stĺpcami ZVOLTE „PriemernýCostofCustomer“ AKO Cost_According_To_Customers, [11], [22], [33] OD (VYBERTE ID zákazníka, Náklady OD Dodávateľov) AKO PIVOT Zdrojovej tabuľky (AVG (Náklady) PRE ID zákazníka v ( [11], [22], [33])) AKO Kontingenčná tabuľka

Výkon:

Poznámka: Keď použijete agregačné funkcie s PIVOT sa pri výpočte agregácie nebudú brať do úvahy hodnoty null.

To bol základný príklad, ale poďme teraz pochopiť, ako fungovala klauzula PIVOT.

Fungovanie klauzuly PIVOT

Ako môžete odkázať vyššie, aby ste vytvorili PIVOT TABLE, musíte postupovať podľa nasledujúcich krokov:

• Vyberte stĺpce, ktoré sa majú otáčať
• Potom vyberte zdrojovú tabuľku.
• Použite operátor PIVOT a potom použite agregačné funkcie.
• Uveďte kontingenčné hodnoty.

Vyberte stĺpce, ktoré sa majú otáčať

Spočiatku musíme určiť polia, ktoré sa majú zahrnúť do našich výsledkov. V našom príklade som považoval stĺpec AverageCostofCustomer v kontingenčnej tabuľke. Potom sme vytvorili ďalšie tri stĺpce s hlavičkami stĺpcov 11, 22 a 33. Príklad -

VYBERTE 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33]

Vyberte zdrojovú tabuľku

Ďalej musíte určiť príkaz SELECT, ktorý vráti zdrojové údaje pre kontingenčnú tabuľku. V našom príklade vraciame identifikáciu zákazníka a náklady z tabuľky Dodávatelia.

(VYBERTE ID zákazníka, náklady od dodávateľov) AKO Zdrojová tabuľka

Použite operátor PIVOT a potom použite agregačné funkcie

Ďalej musíte určiť agregačnú funkciu, ktorá sa má použiť pri vytváraní kontingenčnej tabuľky. V našom príklade som na výpočet priemerných nákladov použil funkciu AVG.

PIVOT (AVG (náklady)

Uveďte kontingenčné hodnoty

Na záver musíte spomenúť hodnoty, ktoré musia byť obsiahnuté vo výslednej kontingenčnej tabuľke. Tieto hodnoty sa použijú ako nadpisy stĺpcov v kontingenčnej tabuľke.

FOR CustomerID IN ([11], [22], [33])) AKO Kontingenčná tabuľka

Takto fungujú operátori PIVOT. Ďalej v tomto článku venujeme SQL PIVOT, pochopme, aké je to odlišné od SQL UNPIVOT.

SQL UNPIVOT

Operátor SQL UNPIVOT sa používa na vykonávanie opačnej operácie ako operácia PIVOT. Používa sa na rotáciu údajov stĺpca do údajov na úrovni riadku. Syntax programu UNPIVOT je podobná syntaxe programu PIVOT. Jediný rozdiel je v tom, že musíte použiť „ UNPIVOT ” .

Príklad:

Vytvorme tabuľku so stĺpcami SupplierID, AAA, BBB a CCC. Vložte tiež niekoľko hodnôt.

VYTVORIŤ TABUĽKU vzorkovateľná (SupplierID int, AAA int, BBB int, CCC int) GO INSERT INTO samplingable VALUES (1,3,5,6) INSERT INTO samplingable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) GO

Výkon:

Teraz, povedzme, chceme otočiť tabuľku. Môžete to urobiť pomocou nasledujúceho kódu:

VYBERTE ID dodávateľa, zákazníkov, produkty OD (VYBERTE DODÁVATEĽA AAA, BBB, CCC OD vzorkovania) p UNPIVOT (Produkty PRE ZÁKAZNÍKOV V (AAA, BBB, CCC)) AKO PRÍKLAD GO

Takto môžete používať SQL PIVOT a UNPIVOT. Týmto sa dostávame k ukončeniu tohto článku. Dúfam, že ste pochopili, ako používať SQL. Ak sa chcete dozvedieť viac o MySQL a spoznajte túto relačnú databázu open-source, potom si pozrite našu ktorá prináša živé školenie vedené inštruktorom a skúsenosti s projektmi v reálnom živote. Toto školenie vám pomôže pochopiť MySQL do hĺbky a pomôže vám dosiahnuť osvojenie si témy.

Máte na nás otázku? Uveďte to, prosím, v sekcii komentárov tohto článku o SQL Pivote a ozvem sa vám.