FRA – Bruker PIVOT og UNPIVOT
- 14.10.2019
- 5 minutter å lese
-
-
V -
c -
M -
i -
M -
+7
-
Gjelder: 
SQL Server (alle støttede versjoner) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallell datalager
Du kan bruke PIVOT og UNPIVOT relasjonsoperatører til endre et tabellverdig uttrykk til en annen tabell. PIVOT roterer et tabellverdig uttrykk ved å snu de unike verdiene fra en kolonne i uttrykket til flere kolonner i utgangen. Og PIVOT kjører aggregasjoner der de «kreves på gjenværende kolonneverdier som ønskes i den endelige utgangen. UNPIVOT utfører motsatt operasjon til PIVOT ved å rotere kolonner i et tabellverdig uttrykk til kolonneverdier.
Syntaksen for PIVOT gir er enklere og mer lesbar enn syntaksen som ellers kan være spesifisert i en kompleks serie med SELECT...CASE -uttalelser. For FULL beskrivelse av syntaksen for PIVOT, se FROM (Transact-SQL).
Syntaks
Følgende syntaks oppsummerer hvordan operatøren PIVOT brukes.
Merknader
Kolonneidentifikatorene i UNPIVOT -satsen følger katalogsorteringen. For SQL-database er sorteringen alltid SQL_Latin1_General_CP1_CI_AS. For SQL Server delvis databaser, er sorteringen alltid Latin1_General_100_CI_AS_KS_WS_SC. Hvis kolonnen er kombinert redigert med andre kolonner, så kreves det en sorteringsklausul (COLLATE DATABASE_DEFAULT) for å unngå konflikter.
Grunnleggende PIVOT-eksempel
Følgende kodeeksempel produserer en to-kolonnetabell som har fire rader.
Her er resultatsettet.
Ingen produkter er definert med tre DaysToManufacture.
Følgende kode viser det samme resultatet, svingt slik at DaysToManufacture verdier blir kolonneoverskriftene. En kolonne er gitt i tre dager, selv om resultatene er NULL.
Her er resultatsettet .
Kompleks PIVOT-eksempel
Et vanlig scenario der PIVOT kan være nyttig når du vil generere kryss-tabellrapporter for å gi et sammendrag av dataene. Anta for eksempel at du vil spørre PurchaseOrderHeader -tabellen i AdventureWorks2014 eksempeldatabasen for å bestemme antall innkjøpsordrer som er gitt av visse ansatte. Følgende spørring gir denne rapporten, bestilt av leverandør.
Her er et delvis resultatsett.
Resultatene som returneres av dette undervalget uttalelse er svingt i EmployeeID -kolonnen.
De unike verdiene som returneres av EmployeeID kolonne blir felt i det endelige resultatsettet. Som sådan er det «en kolonne for hver EmployeeID nummer spesifisert i pivotklausulen: i dette tilfellet ansatte 250, 251, 256, 257, og 260. div id = «c36dcf6b3b»>
-kolonnen fungerer som verdikolonnen, mot hvilken kolonnene som returneres i den endelige utgangen, som kalles grupperingskolonnene, er gruppert. I dette tilfellet blir grupperingskolonnene samlet av COUNT -funksjon. Legg merke til at det vises en advarsel som indikerer at nullverdier som vises i PurchaseOrderID -kolonnen ikke ble tatt i betraktning når du beregner COUNT for hver ansatt.
Viktig
Når samlede funksjoner brukes med PIVOT , blir ikke nullverdier i verdikolonnen tatt i betraktning når du beregner en aggregering.
UNPIVOT Eksempel
UNPIVOT utfører nesten den omvendte operasjonen av PIVOT, ved å rotere kolonner i rader. Anta at tabellen produsert i forrige eksempel er lagret i databasen som pvt, og du vil rotere kolonneidentifikatorene Emp1, Emp2, Emp3, Emp4, og Emp5 i radverdier som tilsvarer en bestemt leverandør. Som sådan må du identifisere to ekstra kolonner.Kolonnen som inneholder kolonneverdiene du roterer (Emp1, Emp2, …) vil hete Employee, og kolonnen som inneholder verdiene som for øyeblikket eksisterer under kolonnene som roteres, vil hete Orders. Disse kolonnene tilsvarer pivot_column og value_column henholdsvis i Transact-SQL-definisjonen. Her er spørringen.
Her er et delvis resultatsett.
Legg merke til at UNPIVOT er ikke det omvendte av PIVOT. PIVOT utfører en aggregering og fletter mulige flere rader til en enkelt rad i utdataene. UNPIVOT reproduserer ikke det opprinnelige tabellverdige uttrykksresultatet fordi rader er slått sammen. Også nullverdier i inngangen til UNPIVOT forsvinner i utdataene. Når verdiene forsvinner, viser det at det kan ha vært originale nullverdier i inngangen før PIVOT -operasjonen.
Sales.vSalesPersonSalesByFiscalYears -visning i AdventureWorks2012-eksempeldatabasen bruker PIVOT for å returnere det totale salget for hver selger, for hvert regnskapsår. Å skriptere visningen i SQL Server Management Studio , i Object Explorer, finn visningen under Views-mappen for AdventureWorks2012-databasen. Høyreklikk på visningsnavnet, og velg deretter Skriptvisning som.
Se også
FROM (Transact -SQL)
CASE (Transact-SQL)