SQL Pivot - vedeti, kako pretvoriti vrstice v stolpce

Relacijski shranite ogromne količine podatkov v obliki tabel. Te tabele imajo lahko poljubno število vrstic in stolpcev. Kaj pa, če bi morali podatke na ravni vrstice spremeniti v stolpčne? No, v tem članku o SQL Pivot vam bom pokazal, kako lahko pretvorite vrstice v stolpec v strežniku SQL.

V tem članku bodo obravnavane naslednje teme:

• Kaj je PIVOT v SQL?
• Sintaksa
• Primer
• Delovanje klavzule PIVOT
• SQL UNPIVOT

Kaj je PIVOT v SQL?

PIVOT se uporablja za vrtenje vrednosti tabele s pretvorbo enoličnih vrednosti enega stolpca v več stolpcev. Uporablja se za vrtenje vrstic na vrednosti stolpcev in po potrebi za seštevanje vrednosti preostalih stolpcev.

UNPIVOT pa se uporablja za izvajanje nasprotnih operacij. Torej se uporablja za pretvorbo stolpcev določene tabele v vrednosti stolpcev.

Nadaljujemo v tem članku, dajmo razumeti sintakso SQL Pivot.

Sintaksa:

SELECT NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnNROM FROM (SELECT poizvedba, ki proizvaja podatke) AS [ali alias za začetno poizvedbo] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName stolpca, katere vrednosti bodo postale glave stolpcev] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last vrteči se stolpec])) AS [vzdevek za vrtilno tabelo]

Tukaj,lahko uporabite tudi Klavzula ORDER BY za razvrščanje vrednosti v naraščajočem ali padajočem vrstnem redu. Zdaj, ko veste, kaj je PIVOT v SQL in njegova osnovna sintaksa, pojdimo naprej in si oglejte, kako ga uporabiti.

Primeri

Za boljše razumevanje bom upošteval naslednjo tabelo, da vam razložim vse primere.

kako obrniti številko v pythonu

Tabela dobaviteljev:

Napišite preprosto poizvedbo, s katero bomo ugotovili povprečne stroške, ki jih je porabila posamezna stranka.

IZBERITE ID stranke, AVG (stroški) KAKO povprečno ceno kupca OD DObaviteljev SKUPINA PO ID-ju kupca

Izhod:

Zdaj recimo, da želimo vrteti zgornjo tabelo. Tu bodo vrednosti stolpcev CustomerID postale glave stolpcev.

- Ustvari vrtilno tabelo z eno vrstico in tremi stolpci IZBERI 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33] FROM (SELECT CustomerID, Cost FROM Dobavitelji) KOT SourceTable PIVOT (AVG (Cost) FOR CustomerID IN ( [11], [22], [33])) KOT vrtilna tabela

Izhod:

Opomba: Ko uporabljate agregatne funkcije pri PIVOT se med izračunom agregacije vrednosti null ne upoštevajo.

No, to je bil osnovni primer, vendar naj zdaj razumemo, kako je delovala klavzula PIVOT.

Delovanje klavzule PIVOT

Kot se lahko sklicujete zgoraj, morate za ustvarjanje PIVOTNE MIZE slediti spodnjim korakom:

• Izberite stolpce za vrtenje
• Nato izberite izvorno tabelo.
• Uporabite operator PIVOT in nato uporabite sestavljene funkcije.
• Omenite vrtilne vrednosti.

Izberite stolpce za vrtenje

Sprva moramo določiti polja, ki bodo vključena v naše rezultate. V našem primeru sem upošteval stolpec AverageCostofCustomer v vrtilni tabeli. Nato smo ustvarili še tri stolpce z naslovi stolpcev 11, 22 in 33. Primer-

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

Izberite izvorno tabelo

Nato morate določiti stavek SELECT, ki bo vrnil izvorne podatke za vrtilno tabelo. V našem primeru vrnemo ID stranke in stroške iz tabele dobaviteljev.

(IZBERITE ID stranke, stroški dobaviteljev) KOT izvorna tabela

Uporabite operator PIVOT in nato uporabite sestavljene funkcije

Nato morate določiti agregatno funkcijo, ki se bo uporabljala med ustvarjanjem vrtilne tabele. V našem primeru sem za izračun povprečnih stroškov uporabil funkcijo AVG.

PIVOT (AVG (strošek)

Omenite vrtilne vrednosti

Na koncu morate omeniti še vrednosti, ki jih je treba vključiti v nastalo vrtilno tabelo. Te vrednosti bodo uporabljene kot naslovi stolpcev v vrtilni tabeli.

FOR CustomerID IN ([11], [22], [33])) KOT vrtilna tabela

Tako delujejo operaterji PIVOT. Nadaljujemo v tem članku o SQL PIVOT, da bomo razumeli, kako drugačen je od SQL UNPIVOT.

SQL UNPIVOT

Operater SQL UNPIVOT se uporablja za izvajanje operacij, ki so nasprotne operacijam PIVOT. Uporablja se za vrtenje podatkov stolpcev v podatke na ravni vrstice. Sintaksa UNPIVOT je podobna sintaksi PIVOT. Edina razlika je v tem, da morate uporabiti ' UNPIVOT ' .

Primer:

Ustvarimo tabelo s stolpci SupplierID, AAA, BBB in CCC. Vstavite tudi nekaj vrednosti.

USTVARI VZORČENO TABELO (ProviderID int, AAA int, BBB int, CCC int) GO INSERT INTO samplema VALUES (1,3,5,6) INSERT INTO sampleable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) GO

Izhod:

Zdaj, recimo, želimo odstraniti tabelo. Če želite to narediti, se lahko sklicujete na naslednjo kodo:

IZBERI ID dobavitelja, kupce, izdelke IZ (IZBERI dobaviteljaD, AAA, BBB, CCC IZ vzorca) p UNPIVOT (Izdelki za kupce v (AAA, BBB, CCC)) Kot primer GO

Tako lahko uporabite SQL PIVOT in UNPIVOT. S tem smo prišli do konca tega članka. Upam, da ste razumeli, kako uporabljati SQL. Če želite izvedeti več o MySQL in spoznajte to odprtokodno relacijsko bazo podatkov, nato si oglejte našo ki prihaja z usposabljanjem pod vodstvom inštruktorjev v živo in izkušnjami iz resničnega življenja Ta trening vam bo pomagal poglobljeno razumeti MySQL in vam pomagal doseči obvladovanje zadeve.

Imate vprašanje za nas? Prosimo, omenite ga v oddelku za komentarje tega članka o SQL Pivot in se vam oglasim.