Denne artikkelen vil gi en fullstendig oversikt over bruk av PIVOT- og UNPIVOT-operatørene i SQL Server. PIVOT- og UNPIVOT-operatorene ligner på relasjonsoperatorene som tillater transformerer det tabellverdide uttrykket til en annen tabell . Begge operatørene genererer flerdimensjonal rapportering som hjelper til med å kombinere og sammenligne en stor mengde data raskt.
Vi kan bruke PIVOT-operatør når vi trenger å transformere tabell-verdier uttrykk. Det deler opp unike verdier fra én kolonne til mange kolonner i sluttresultatet. Det også aggregater de resterende kolonneverdiene som kreves i det endelige resultatet. UNPIVOT-operatør konverterer data fra kolonner i et tabellverdiuttrykk til kolonneverdier, som er inversen til PIVOT.
La oss forstå det ved hjelp av det enkle diagrammet nedenfor:
På venstre side av denne figuren kan vi se originalt datasett , som har tre kolonner: år, region, og Salg . Deretter kan vi se PIVOT-bordet på høyre side, som er konstruert ved å rotere Region (rader) til nord og sør (kolonner) . Etter å ha konvertert rader til kolonner, kan vi gjøre en aggregering av salgskolonneverdier for hvert skjæringspunkt mellom kolonnene og radene i PIVOT-tabellen.
La oss først lage en tabell med navnet pivot_demo for å demonstrere PIVOT- og UNPIVOT-operatørene. Følgende setning oppretter en ny tabell i vår spesifiserte database:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Deretter setter du inn noen data i denne tabellen som nedenfor:
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);
Vi kan verifisere dataene ved å bruke SELECT-setningen. Vi vil få utgangen nedenfor:
unnslippe karakter java
PIVOT-operatør
Denne operatoren brukes til å rotere tabellverdiuttrykk. Den ble først introdusert i SQL Server 2005-versjonen. Den konverterer data fra rader til kolonner. Den deler de unike verdiene fra én kolonne i mange kolonner og samler deretter de gjenværende kolonneverdiene som kreves i det endelige resultatet.
Vi må følge følgende trinn for å lage en PIVOT-tabell:
- Velg basisdatasettet for pivotering.
- Lag midlertidige resultater ved hjelp av en avledet tabell eller CTE (vanlig tabelluttrykk).
- Benytt PIVOT-operatøren.
Syntaks
Følgende syntaks illustrerer bruken av PIVOT i SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Hvis vi bryter dette skriptet, kan vi se at det har to separate seksjoner. Den første delen velger data fra hovedtabellen, og den andre delen bestemmer hvordan PIVOT-tabellen skal konstrueres. Den andre delen inneholder også noen spesielle nøkkelord som SUM, FOR og IN. La oss se betydningen av disse nøkkelordene i PIVOT-operatøren.
SUM
Denne operatøren er vant til samle verdiene fra den angitte kolonnen som skal brukes i PIVOT-tabellen. Vi må bruke den med PIVOT-operatøren for å få de aggregerte kolonnevisningene for verdiseksjonene.
FOR nøkkelord
eksempel binært søketre
Dette nøkkelordet brukes for PIVOT-tabellsetningen til instruer PIVOT-operatøren på hvilken kolonne PIVOT-funksjonen skal brukes. I utgangspunktet indikerer det kolonnenavnene som vil transformeres fra rader til kolonner.
IN nøkkelord
Dette nøkkelordet viser alle de unike verdiene fra PIVOT-kolonnen for å vises som kolonnene i PIVOT-tabellen.
Eksempel
La oss forstå det ved hjelp av forskjellige eksempler.
1. Følgende setning velger først år, nord og sør som basisdata for pivotering. Deretter oppretter du et midlertidig resultat ved å bruke den utledede tabellen og bruker til slutt PIVOT-operatøren for å generere den endelige utgangen. Denne utgangen bestilles også i det stigende året.
trådsynkronisering
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
Utførelse av denne setningen vil produsere utgangen nedenfor. Her kan vi se beregnet sum av region Nord og Sør omsetning tilsvarende årsverdiene .
2. Dette er et annet eksempel der vi skal beregne summen av salg for hvert år som tilsvarer regionverdiene:
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;
Utførelse av denne erklæringen vil produsere en feil fordi vi ikke kan spesifisere den numeriske verdien som et kolonnenavn direkte.
SQL Server lar oss imidlertid unngå dette problemet ved å bruke parentesene før hver heltallsverdi. Den oppdaterte setningen vises i følgende kodebit:
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;
Denne uttalelsen ble utført vellykket og viser den beregnede summen av salg for hvert år som tilsvarer regionverdiene:
3. Det forrige eksemplet på å få en PIVOT-tabell er nyttig når vi er klar over alle mulige PIVOT-kolonneverdier. Men anta at antallet kolonner økes i det kommende året. Med tanke på det foregående eksemplet har vi 2010, 2011 og 2012 år som PIVOT-kolonner. Det er imidlertid ingen garanti for at disse kolonnene ikke vil endre seg i fremtiden. Hva skjer hvis vi har data fra 2013 eller 2014, eller kanskje enda mer? I slike tilfeller må vi bruke dynamisk PIVOT-tabell spørsmål for å fikse dette problemet.
Den dynamiske PIVOT-tabellspørringen innkapsler hele PIVOT-skriptet i en lagret prosedyre. Denne prosedyren vil gi justerbare alternativer, slik at vi kan endre kravene våre ved å endre noen få parameteriserte verdier.
Følgende SQL-kode forklarer hvordan den dynamiske PIVOT-tabellen fungerer. I dette skriptet har vi først hentet alle distinkte verdier fra PIVOT-kolonnen og deretter skrevet en SQL-setning for kjøring med PIVOT-spørringen under kjøring. La oss se utdataene etter å ha utført dette skriptet:
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
I dette skriptet har vi laget to parameteriserte variabler. Beskrivelsen er gitt nedenfor:
@PivotColumn : Denne variabelen vil ta kolonnenavnet fra den opprinnelige tabellen som PIVOT-tabellen er opprettet på. For eksempel , her viser kolonnen 'Region' alle regionene som er tilgjengelige i kolonnene.
@PivotList : Denne variabelen vil ta kolonnelisten som vi ønsker å vise som en utdatakolonne i PIVOT-tabellen.
Utførelse av dynamisk lagret prosedyre
Etter vellykket opprettelse av den dynamiske lagrede prosedyren, er vi klare til å utføre den. Følgende setning brukes til å kalle den dynamiske lagrede prosedyren for å vise PIVOT-tabellen ved kjøretid:
forskjellen mellom en gigabyte og en megabyte
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Her har vi nå spesifisert kolonnenavnet ' Region ' som den første parameteren og PIVOT-kolonnelisten som den andre parameteren. Utførelse av skriptet vil vise følgende utdata:
Nå kan vi legge til flere kolonner i fremtiden ved kjøretid for å vise PIVOT-tabellen, noe som ikke er mulig med de to første eksemplene.
UNPIVOT-operatør
Det er den omvendte metoden til PIVOT-operatøren i SQL Server. Det bemanner denne operatøren utfører motsatt drift av PIVOT ved å konvertere data fra kolonner til rader. UNPIVOT-operatøren roterer også PIVOT-tabellen til den vanlige tabellen. Den ble først introdusert i SQL Server 2005-versjonen.
Syntaks
Følgende syntaks illustrerer UNPIVOT i SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Eksempel
La oss forstå hvordan du UNPIVOT PIVOT-operasjonen med eksempler. Vi vil først lage en originalt bord og PIVOT-bord og deretter brukt UNPIVOT-operator på denne tabellen.
Følgende kodebit erklærer først en midlertidig tabellvariabel @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Deretter vil vi sette inn verdier i denne tabellen som nedenfor:
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
Nå kan vi utføre UNPIVOT-operasjon ved å bruke setningen nedenfor:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Kjøring av kodebiten vil returnere følgende utdata:
Kodebiten nedenfor er et annet eksempel for først å utføre PIVOT-operasjon og deretter UNPIVOT-operasjon på samme tabell i en enkelt spørring:
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
Kjøring av kodebiten vil vise samme utdata:
parameter i shell script
MERK: UNPIVOT-prosessen er en omvendt operasjon av PIVOT-prosedyren, men det er ikke en eksakt reversering. Fordi rader har blitt slått sammen når PIVOT beregner aggregatet og kombinert mange rader til en enkelt rad i resultatet, kan derfor ikke UNPIVOT-operasjonen gjøre tabellen lik originalen. Imidlertid, hvis PIVOT-operatoren ikke slår sammen mange rader til en enkelt rad, kan UNPIVOT-operatoren hente den opprinnelige tabellen fra PIVOT-utgangen.
Konklusjon
Denne artikkelen vil gi en fullstendig oversikt over PIVOT- og UNPIVOT-operatorer i SQL Server og konvertere et tabelluttrykk til et annet. Den bør aldri glemme at UNPIVOT er den omvendte operasjonen til PIVOT, men det er ikke den nøyaktige inversen av PIVOT-resultatet.