Den här artikeln ger en fullständig översikt över hur PIVOT- och UNPIVOT-operatorerna används i SQL Server. PIVOT- och UNPIVOT-operatorerna liknar de relationsoperatorer som tillåter omvandla det tabellvärdade uttrycket till en annan tabell . Båda operatörerna genererar flerdimensionell rapportering som hjälper till att snabbt kombinera och jämföra en stor mängd data.
Vi kan använda PIVOT-operatör när vi behöver transformera tabellvärdade uttryck. Det delar upp unika värden från en kolumn till många kolumner i slutresultatet. Det är också aggregat de återstående kolumnvärdena som krävs i slutresultatet. UNPIVOT-operatör konverterar data från kolumner i ett tabellvärde uttryck till kolumnvärden, vilket är inversen av PIVOT.
Låt oss förstå det med hjälp av det enkla diagrammet nedan:
På den vänstra sidan av denna figur kan vi se originaldatauppsättning , som har tre kolumner: år, region, och Försäljning . Därefter kan vi se PIVOT-bordet på höger sida, som är konstruerat genom att rotera Region (rader) till norr och söder (kolumner) . Efter att ha konverterat rader till kolumner kan vi göra en aggregat av värden för försäljningskolumnen för varje skärningspunkt mellan kolumnerna och raderna i PIVOT-tabellen.
Låt oss först skapa en tabell med namnet pivot_demo för att demonstrera PIVOT- och UNPIVOT-operatörerna. Följande sats skapar en ny tabell i vår specificerade databas:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Infoga sedan några data i den här tabellen enligt nedan:
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 verifiera data med hjälp av SELECT-satsen. Vi kommer att få följande utdata:
PIVOT-operatör
Denna operator används för att rotera tabellvärdade uttryck. Det introducerades först i SQL Server 2005-versionen. Den konverterar data från rader till kolumner. Den delar upp de unika värdena från en kolumn i många kolumner och aggregerar sedan de återstående kolumnvärdena som krävs i slutresultatet.
Vi måste följa följande steg för att skapa en PIVOT-tabell:
- Välj basdatauppsättningen för pivotering.
- Skapa tillfälliga resultat med hjälp av en härledd tabell eller CTE (common table expression).
- Använd PIVOT-operatören.
Syntax
Följande syntax illustrerar användningen av PIVOT i SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Om vi bryter det här skriptet kan vi se att det har två separata avsnitt. Det första avsnittet väljer data från huvudtabellen och det andra avsnittet bestämmer hur PIVOT-tabellen kommer att konstrueras. Den andra delen innehåller också några speciella nyckelord som SUM, FOR och IN. Låt oss se innebörden av dessa nyckelord i PIVOT-operatorn.
BELOPP
Denna operatör är van vid aggregera värdena från den angivna kolumnen som ska användas i PIVOT-tabellen. Vi måste använda den med PIVOT-operatorn för att få de aggregerade kolumnvisningarna för värdesektionerna.
FÖR Nyckelord
Detta nyckelord används för PIVOT-tabellsatsen till instruera PIVOT-operatören på vilken kolumn PIVOT-funktionen ska användas. I grund och botten indikerar det kolumnnamnen som kommer att förvandlas från rader till kolumner.
IN Nyckelord
Detta nyckelord listar alla unika värden från PIVOT-kolumnen för att visas som kolumnerna i PIVOT-tabellen.
Exempel
Låt oss förstå det med hjälp av olika exempel.
1. Följande sats väljer först kolumnen År, Nord och Syd som basdata för pivotering. Skapa sedan ett tillfälligt resultat med hjälp av den härledda tabellen och använd slutligen PIVOT-operatorn för att generera den slutliga utmatningen. Denna utgång beställs också under det stigande året.
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
Om du kör detta uttalande kommer det att produceras nedan. Här kan vi se beräknad summa av Nord- och Sydregionens försäljning motsvarande årsvärdena .
2. Detta är ett annat exempel där vi kommer att beräkna summan av försäljningen för varje år som motsvarar regionvärdena:
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;
Att utföra detta uttalande kommer skapa ett fel eftersom vi inte kan ange det numeriska värdet som ett kolumnnamn direkt.
Men SQL Server tillåter oss att undvika detta problem genom att använda parenteser före varje heltalsvärde. Det uppdaterade uttalandet visas i följande kodavsnitt:
python sortering tuplar
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;
Detta uttalande utfördes framgångsrikt och visar den beräknade summan av försäljningen för varje år som motsvarar regionvärdena:
3. Det tidigare exemplet på att få en PIVOT-tabell är till hjälp när vi är medvetna om alla möjliga PIVOT-kolumnvärden. Men anta att antalet kolumner utökas under det kommande året. Med tanke på det föregående exemplet har vi 2010, 2011 och 2012 år som PIVOT-kolumner. Det finns dock ingen garanti för att dessa kolumner inte kommer att förändras i framtiden. Vad händer om vi har data från 2013 eller 2014, eller kanske ännu mer? I sådana fall kommer vi att behöva använda dynamisk PIVOT-tabell frågor för att lösa detta problem.
Den dynamiska PIVOT-tabellfrågan kapslar in hela PIVOT-skriptet i en lagrad procedur. Denna procedur kommer att tillhandahålla justerbara alternativ, vilket gör att vi kan modifiera våra krav genom att ändra några parametriserade värden.
Följande SQL-kod förklarar hur den dynamiska PIVOT-tabellen fungerar. I det här skriptet har vi först hämtat alla distinkta värden från PIVOT-kolumnen och sedan skrivit en SQL-sats för exekvering med PIVOT-frågan vid körning. Låt oss se resultatet efter att ha kört det här 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 det här skriptet har vi skapat två parameteriserade variabler. Dess beskrivning ges nedan:
@Pivotkolumn : Denna variabel tar kolumnens namn från den ursprungliga tabellen som PIVOT-tabellen skapas på. Till exempel , här visar kolumnen 'Region' alla tillgängliga regioner i kolumnerna.
@PivotList : Denna variabel tar kolumnlistan som vi vill visa som en utdatakolumn i PIVOT-tabellen.
Utförande av dynamisk lagrad procedur
Efter framgångsrikt skapande av den dynamiska lagrade proceduren är vi redo att köra den. Följande sats används för att anropa den dynamiska lagrade proceduren för att visa PIVOT-tabellen vid körning:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Här har vi nu specificerat kolumnnamnet ' Område ' som den första parametern och PIVOT-kolumnlistan som den andra parametern. När du kör skriptet visas följande utdata:
Nu kan vi lägga till fler kolumner i framtiden vid körning för att visa PIVOT-tabellen, vilket inte är möjligt med de två första exemplen.
UNPIVOT-operatör
Det är den omvända metoden för PIVOT-operatören i SQL Server. Det bemannar denna operatör utför motsatt funktion av PIVOT genom att konvertera data från kolumner till rader. UNPIVOT-operatorn roterar också PIVOT-tabellen till den vanliga tabellen. Det introducerades först i SQL Server 2005-versionen.
Syntax
Följande syntax illustrerar 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)
Exempel
Låt oss förstå hur man UNPIVOT PIVOT-operationen med exempel. Vi kommer först att skapa en originalbord och PIVOT-bord och använde sedan UNPIVOT-operatorn på denna tabell.
Följande kodavsnitt deklarerar först en temporär tabellvariabel @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Därefter kommer vi att infoga värden i den här tabellen enligt nedan:
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
Nu kan vi utföra UNPIVOT-operation med hjälp av följande uttalande:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Om du kör kodavsnittet returneras följande utdata:
jämförelse av lejon och tiger
Kodavsnittet nedan är ett annat exempel för att först utföra PIVOT-operation och sedan UNPIVOT-operation på samma tabell inom en enda fråga:
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
Om du kör kodavsnittet visas samma utdata:
OBS: UNPIVOT-processen är en omvänd operation av PIVOT-proceduren, men det är inte en exakt omkastning. Eftersom rader har slagits samman när PIVOT beräknar aggregatet och kombinerar många rader till en enda rad i resultatet, kan därför UNPIVOT-operationen inte göra tabellen som originalet. Men om PIVOT-operatorn inte slår samman många rader till en enda rad, kan UNPIVOT-operatorn hämta den ursprungliga tabellen från PIVOT-utgången.
Slutsats
Den här artikeln kommer att ge en fullständig översikt över PIVOT- och UNPIVOT-operatorer i SQL Server och konvertera ett tabelluttryck till ett annat. Man får aldrig glömma att UNPIVOT är den omvända operationen av PIVOT, men det är inte den exakta inversen av PIVOT-resultatet.