SQLShack

i den här artikeln går vi igenom SQL Pivot och SQL Unpivot-operatörerna och hur de kan vara användbara för att transponera SQL Server-data. Vi diskuterar också både statiska och dynamiska sätt att använda pivot-och UNPIVOT-relationella operatörer som kan användas för att omvandla aggregerade distinkta värden som kolumner i resultatuppsättningen genom att ange alla kolumnvärden i pivot in-satsen.

SQL Server pivot introduktion

det finns flera sätt att transponera en dataset från rader till kolumner och kolumner till rader. SQL Pivot är en av de tekniker som tillåter transponering av rader till kolumner och utför möjliga aggregeringar längs vägen. SQL PIVOT och SQL UNPIVOT relationella operatörer transponerar en tabellvärderad tvådimensionell data till en annan form av data. SQL PIVOT transponerar ett tabellvärderat uttryck från en unik uppsättning värden från en kolumn till flera kolumner i utdata och utför aggregeringar. SQL UNPIVOT utför motsatt funktion av SQL PIVOT genom att omvandla kolumnerna i ett tabellvärderat uttryck till kolumnvärden.

i de flesta fall är den statiska svängningstekniken tillräcklig för affärskravet. Till exempel den månatliga försäljningsprognosen, årlig försäljningsfördelning, kvartalsvis försäljningsaggregering, etc, där kolumnerna i klausulen förblir statiska. I vissa andra fall behöver vi detaljerade detaljer och tabellvärdeuttrycket är mer dynamiskt i naturen och hela tiden ingår ny uppsättning av uttrycket i tabellen då dynamisk PIVOT skulle vara det bästa valet.

Obs: i ovanstående skildring kan vi se att kolumnvärdena roteras från vertikalt till horisontellt och UNPIVOTING är som att rotera det från horisontellt till vertikalt.

Syntax

SELECT <non-pivoted column>,

AS <column alias>,

AS <column alias>,

…

AS <column alias>

FROM

(

<SELECT QUERY>)

AS <Alias for temporary data set>

PIVOT

(

<Aggregate function>( Aggregate column)

FOR

IN ( , ,

… )

) AS <Pivottabellalias>

< ordning efter klausul>;

komma igång med SQL pivot

vi ska börja med att svänga och un-pivoting data. Nu förstår du vilka pivot-och UNPIVOT-operatörer som finns i SQL Server. Enkelt uttryckt är det bara ett annat sätt att beskriva processen att vrida distinkta radvärden i sina egna kolumner kallas svängning. Och processen att vrida kolumner i rader är känd som en un-pivotering.

exempel 1: Så här utför du en SQL Pivot-operation

Låt oss avbilda exempeldatasetet som härrör från AdventureWorks2014-databasen. I det här fallet har du bara en enkel dataset med SalesYear-kolumnen längs det kvantitativa värdet TotalSales.

nu är datasetet klart för svängning. Du har SalesYear i den första kolumnen, TotalSales i den andra kolumnen. Om du bad om att överföra data måste du ta varje distinkt år i kolumnen och dessa kolumner blir rubriken för pivotkolumnerna. Så 2011 blir den första kolumnen, 2012 blir sin egen kolumn och så vidare. De faktiska värdena från nästa kolumn Totaltförsäljning, de håller fast vid pivotkolumnrubrikerna. Nu skulle svängbordet se ut nedan.

följande bild visar indata, pivot-syntax, SQL-pivot och utgångsresultat.

exempel 2: Så här utför du en SQL Unpivot-operation

Låt oss nu titta i andra riktningen, un-pivoting, vilket är så enkelt som att gå från horisontellt till vertikalt. I det här fallet skulle du ta alla de distinkta kolumnerna som väljs, och det skulle göra dessa kolumnrubriker till sina egna rader. Svängning är som att rotera från vertikalt till horisontellt. Och un-pivoting är som att rotera från horisontellt till vertikalt.

i följande exempel används den svängda datauppsättningen för att un-pivot värdena. Nyckelstycket här är att det var lätt att göra för att omvandlas eftersom vi kunde skapa ett pivottabell som ett mellansteg och sedan göra omvandlingen med hjälp av unpivot-operationen.

OBS: SQL pivot-operation resulterar i att det aggregerade resultatet transponeras i kolumn men SQL unpivot är inte en exakt återföring att införliva kolumner i de segregerade värdena för rader. Unpivot-operatören delar inte upp de aggregerade resultaten.

exempel 3: Så här får du kvartalsförsäljning med SQL pivot operation

Låt oss se några fler exempel för att förstå svängningskoncepten bättre. I det här fallet ser vi hur man får kvartalsvisa försäljningsdata aggregerade baserat på kvartalsförsäljningen.

innan vi hoppar in i lösningen rekommenderas det alltid att arbeta med att skapa en dataset för PIVOT-operationen.

följande exempel ger den aggregerade månatliga försäljningen fördelad över försäljningsåret.

följande utgång visar basdata

När du är redo med basdata kan du använda pivot-operatören. I följande exempel accepterar in-klausulen ett icke-numeriskt tecken som genererades baserat på DATEPART-värdet. DATEPART-värdet sammanfogas med ’ Q ’ och sedan matas värdena till i klausul i PIVOT operator.

följande produktion är en kvartalsvis uppdelning av försäljningsdata

exempel 4: Hur man får månadsförsäljning med en SQL pivot-operation

låt oss ta en titt på ett annat exempel för att se försäljningsuppdelningsdata baserat på varje månad. I följande exempel genereras in-klausulvärdena som en del av DATEPART-funktionen. DATEPART-värdena matas till i klausul av PIVOT operator.

följande utgång är en månatlig uppdelning av försäljningsdata

exempel 5: Hur man får månadsförsäljning med dynamisk SQL pivot-operation

Låt oss adressera den månatliga uppdelningen av försäljningsdata med dynamisk pivotering. Hittills diskuterade vi statiska pivotoperationer. För att konvertera en statisk pivotoperation till dynamisk måste vi ta bort de hårdkodade värdena från in-satsen. Först, få det distinkta uttrycket från datasetet och förbered sedan en sträng genom att sammanfoga alla uttryck. I följande exempel används @ columnname för att sammanfoga alla uttryck. Det sammanlänkade uttrycket matas till pivot i klausul. Resten är en enkel konvertering av statisk SQL till en dynamisk SQL och ring @DML med sp_executesql lagrad procedur.

det är allt för nu…

wrapping up

hittills har vi visat de grundläggande begreppen och flera exempel på SQL pivot och SQL unpivot.

provet är baserat på SQL Server adventureworks2014-databasen. Du kan prova och testa alla prover själv. Om du har några frågor, Är du välkommen att kommentera nedan…

• författare
• Senaste inlägg

> jag är en databastekniker som har 11+ års rik, praktisk erfarenhet av databasteknik. Jag är Microsoft certifierad professionell och backas upp med en examen i Master of Computer Application.
min specialitet ligger i att designa & implementera lösningar med hög tillgänglighet och plattformsoberoende DB-migrering. De tekniker som för närvarande arbetar med är SQL Server, PowerShell, Oracle och MongoDB.
Visa alla inlägg av Prashanth Jayaram

Senaste inlägg av Prashanth Jayaram (se alla)

• en snabb översikt över databasrevision i SQL – Januari 28, 2021
• hur man gör det konfigurera Azure Data Sync mellan Azure SQL-databaser och lokal SQL Server – 20 januari 2021
• hur man utför Azure SQL database import/export – operationer med PowerShell-14 januari 2021