Šiame straipsnyje bus pateikta išsami PIVOT ir UNPIVOT operatorių naudojimo SQL Server apžvalga. PIVOT ir UNPIVOT operatoriai yra panašūs į reliacinius operatorius, kurie leidžia lentelės vertės išraišką transformuojant į kitą lentelę . Abu operatoriai generuoja daugiamates ataskaitas, kurios padeda greitai sujungti ir palyginti didelį duomenų kiekį.
Mes galime naudoti PIVOT operatorius kai reikia transformuoti lentelės vertės išraiškas. Jis suskaido unikalias vertes iš vieno stulpelio į daugybę stulpelių galutiniame rezultate. Taip pat agregatai likusios stulpelio reikšmės, reikalingos galutiniame rezultate. UNPIVOT operatorius konvertuoja duomenis iš lentelės vertės išraiškos stulpelių į stulpelių reikšmes, o tai yra atvirkštinė PIVOT.
Supraskime tai naudodami toliau pateiktą paprastą diagramą:
Kairėje šio paveikslo pusėje matome pradinis duomenų rinkinys , kuriame yra trys stulpeliai: Metai, regionas, ir Pardavimai . Toliau dešinėje pusėje matome PIVOT lentelę, kuri sukonstruota sukant Regionas (eilutės) į šiaurę ir pietus (stulpeliai) . Pavertę eilutes į stulpelius, galime atlikti a Pardavimo stulpelio verčių visuma kiekvienai PIVOT lentelės stulpelių ir eilučių sankirtai.
Pirmiausia sukurkime lentelę pavadinimu pivot_demo parodyti PIVOT ir UNPIVOT operatorius. Šis teiginys sukuria naują lentelę mūsų nurodytoje duomenų bazėje:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Tada į šią lentelę įterpkite kai kuriuos duomenis, kaip nurodyta toliau:
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);
Duomenis galime patikrinti naudodami SELECT teiginį. Gausime žemiau pateiktą išvestį:
PIVOT operatorius
Šis operatorius naudojamas lentelės vertės išraiškoms pasukti. Pirmą kartą jis buvo pristatytas SQL Server 2005 versijoje. Jis konvertuoja duomenis iš eilučių į stulpelius. Jis padalija unikalias reikšmes iš vieno stulpelio į daugybę stulpelių ir sujungia likusias stulpelių reikšmes, kurių reikia galutiniam rezultatui.
Norėdami sukurti PIVOT lentelę, turime atlikti šiuos veiksmus:
- Pasirinkite pagrindinį duomenų rinkinį, skirtą sukimui.
- Sukurkite laikinus rezultatus naudodami išvestinę lentelę arba CTE (bendra lentelės išraiška).
- Pasinaudokite PIVOT operatoriumi.
Sintaksė
Ši sintaksė iliustruoja PIVOT naudojimą SQL serveryje:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Jei sulaužysime šį scenarijų, pamatysime, kad jame yra dvi atskiros dalys. Pirmajame skyriuje parenkami duomenys iš pagrindinės lentelės, o antroje dalyje nustatoma, kaip bus sudaryta PIVOT lentelė. Antroje dalyje taip pat yra keletas specialių raktinių žodžių, tokių kaip SUM, FOR ir IN. Pažiūrėkime šių raktinių žodžių reikšmę PIVOT operatoriuje.
SUMA
Šis operatorius yra įpratęs sumuoti vertes iš nurodyto stulpelio, kuris bus naudojamas PIVOT lentelėje. Turime jį naudoti kartu su PIVOT operatoriumi, kad gautume apibendrintus stulpelių rodmenis verčių sekcijoms.
DĖL Raktažodžio
Šis raktinis žodis naudojamas PIVOT lentelės teiginiui nurodykite PIVOT operatoriui kuriame stulpelyje turėtų būti taikoma PIVOT funkcija. Iš esmės tai nurodo stulpelių pavadinimus, kurie bus transformuojami iš eilučių į stulpelius.
IN Raktažodis
Šis raktinis žodis išvardija visas unikalias vertybes iš PIVOT stulpelio, kad būtų rodomi kaip PIVOT lentelės stulpeliai.
Pavyzdys
Supraskime tai pasitelkę įvairius pavyzdžius.
1. Šis teiginys pirmiausia pasirenka Metų, Šiaurės ir Pietų stulpelius kaip pagrindinius sukimo duomenis. Tada sukurkite laikiną rezultatą naudodami išvestinę lentelę ir galiausiai pritaikykite PIVOT operatorių, kad sugeneruotumėte galutinę išvestį. Ši produkcija taip pat užsakoma Metų didėjimo tvarka.
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
Vykdydami šį pareiškimą gausite toliau pateiktą išvestį. Čia galime pamatyti apskaičiuota Šiaurės ir Pietų regionų pardavimų suma, atitinkanti metų vertes .
2. Tai dar vienas pavyzdys, kai apskaičiuosime kiekvienų metų pardavimų sumą, atitinkančią regiono reikšmes:
normalizavimas duomenų bazėje
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;
Šio pareiškimo vykdymas bus padaryti klaidą nes negalime tiesiogiai nurodyti skaitinės reikšmės kaip stulpelio pavadinimo.
Tačiau SQL Server leidžia mums išvengti šios problemos, naudojant skliaustus prieš kiekvieną sveikojo skaičiaus reikšmę. Atnaujintas pareiškimas rodomas šiame kodo fragmente:
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;
Šis pareiškimas sėkmingai įvykdytas ir rodo apskaičiuotą kiekvienų metų pardavimų sumą, atitinkančią regiono reikšmes:
3. Ankstesnis PIVOT lentelės gavimo pavyzdys yra naudingas, kai žinome visas galimas PIVOT stulpelio reikšmes. Tačiau tarkime, kad ateinančiais metais stulpelių skaičius bus padidintas. Atsižvelgiant į ankstesnį pavyzdį, kaip PIVOT stulpeliai yra 2010, 2011 ir 2012 metai. Tačiau nėra garantijos, kad šie stulpeliai nepasikeis ateityje. Kas nutiks, jei turėsime 2013 ar 2014 m., o gal net daugiau duomenų? Tokiais atvejais turėsime naudoti dinaminė PIVOT lentelė užklausas, kaip išspręsti šią problemą.
Dinaminė PIVOT lentelės užklausa visą PIVOT scenarijų įtraukia į saugomą procedūrą. Ši procedūra suteiks reguliuojamų parinkčių, kurios leis mums pakeisti savo reikalavimus pakeitus keletą parametrų verčių.
Šis SQL kodas paaiškina dinaminės PIVOT lentelės veikimą. Šiame scenarijuje pirmiausia gavome visas skirtingas reikšmes iš PIVOT stulpelio ir tada parašėme SQL sakinį, kad būtų galima vykdyti su PIVOT užklausa vykdymo metu. Pažiūrėkime išvestį po šio scenarijaus vykdymo:
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
Šiame scenarijuje sukūrėme du parametruotus kintamuosius. Jo aprašymas pateikiamas žemiau:
@PivotColumn : Šis kintamasis paims stulpelio pavadinimą iš pradinės lentelės, kurioje sukurta PIVOT lentelė. Pavyzdžiui , čia stulpelyje „Regionas“ rodomi visi stulpeliuose galimi regionai.
@PivotList : Šis kintamasis paims stulpelių sąrašą, kurį norime rodyti kaip išvesties stulpelį PIVOT lentelėje.
Dinaminės saugomos procedūros vykdymas
Sėkmingai sukūrę dinaminę saugomą procedūrą, esame pasiruošę ją vykdyti. Šis teiginys naudojamas dinaminei saugomai procedūrai iškviesti, kad būtų rodoma PIVOT lentelė vykdymo metu:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Čia mes dabar nurodėme stulpelio pavadinimą ' Regionas ' kaip pirmasis parametras, o PIVOT stulpelių sąrašas kaip antrasis parametras. Vykdant scenarijų bus rodoma ši išvestis:
Dabar vykdymo metu ateityje galime pridėti daugiau stulpelių, kad būtų rodoma PIVOT lentelė, o tai neįmanoma naudojant pirmuosius du pavyzdžius.
UNPIVOT operatorius
Tai atvirkštinis PIVOT operatoriaus metodas SQL Server. Tai mans šis operatorius atlieka priešingas PIVOT veikimas konvertuojant duomenis iš stulpelių į eilutes. UNPIVOT operatorius taip pat paverčia PIVOT lentelę į įprastą lentelę. Pirmą kartą jis buvo pristatytas SQL Server 2005 versijoje.
Sintaksė
Ši sintaksė iliustruoja UNPIVOT SQL serveryje:
susietas sąrašas
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Pavyzdys
Su pavyzdžiais suprasime, kaip UNPIVOT operaciją PIVOT. Pirmiausia sukursime an originalus stalas ir PIVOT stalas ir tada šioje lentelėje pritaikė UNPIVOT operatorių.
Šis kodo fragmentas pirmiausia deklaruoja laikinąjį lentelės kintamąjį @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Toliau į šią lentelę įterpsime reikšmes, kaip nurodyta toliau:
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
Dabar galime atlikti UNPIVOT operaciją naudodami toliau pateiktą teiginį:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Vykdant kodo fragmentą bus pateikta tokia išvestis:
Toliau pateiktas kodo fragmentas yra dar vienas pavyzdys, kai vienoje užklausoje pirmiausia atliekama PIVOT operacija, o paskui UNPIVOT operacija toje pačioje lentelėje:
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
Vykdant kodo fragmentą bus rodoma ta pati išvestis:
PASTABA: UNPIVOT procesas yra atvirkštinis PIVOT procedūros veiksmas, tačiau tai nėra tikslus atšaukimas. Kadangi eilutės buvo sujungtos, kai PIVOT apskaičiuoja suvestinę sumą, o rezultate daug eilučių sujungiama į vieną eilutę, todėl UNPIVOT operacija negali padaryti lentelės panašios į originalą. Tačiau jei PIVOT operatorius nesujungia daugelio eilučių į vieną eilutę, UNPIVOT operatorius gali gauti pradinę lentelę iš PIVOT išvesties.
Išvada
Šiame straipsnyje bus pateikta išsami SQL serverio PIVOT ir UNPIVOT operatorių apžvalga ir lentelės išraiškos konvertavimas į kitą. Niekada nereikėtų pamiršti, kad UNPIVOT yra atvirkštinis PIVOT veikimas, tačiau tai nėra tiksli PIVOT rezultato atvirkštinė pusė.