Denne artikel vil give et komplet overblik over brugen af PIVOT- og UNPIVOT-operatørerne i SQL Server. PIVOT- og UNPIVOT-operatorerne ligner de relationelle operatorer, der giver mulighed for at transformere det tabelværdisatte udtryk til en anden tabel . Begge operatører genererer multidimensionel rapportering, der hjælper med at kombinere og sammenligne en stor mængde data hurtigt.
Vi kan bruge PIVOT operatør når vi skal transformere tabelværdiede udtryk. Det splitter unikke værdier fra én kolonne til mange kolonner i det endelige resultat. Det også aggregater de resterende kolonneværdier, der kræves i det endelige resultat. UNPIVOT operatør konverterer data fra kolonner i et tabelværdiudtryk til kolonneværdier, som er det omvendte af PIVOT.
Lad os forstå det ved hjælp af det enkle diagram nedenfor:
På venstre side af denne figur kan vi se originalt datasæt , som har tre kolonner: år, region, og Salg . Dernæst kan vi se PIVOT-bordet i højre side, som er konstrueret ved at dreje på Region (rækker) i nord og syd (kolonner) . Efter at have konverteret rækker til kolonner, kan vi lave en aggregering af salgskolonneværdier for hvert skæringspunkt mellem kolonnerne og rækkerne i PIVOT-tabellen.
Lad os først oprette en tabel med navnet pivot_demo for at demonstrere PIVOT- og UNPIVOT-operatørerne. Følgende sætning opretter en ny tabel i vores specificerede database:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Indsæt derefter nogle data i denne tabel 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 verificere dataene ved hjælp af SELECT-sætningen. Vi får nedenstående output:
escape karakter java
PIVOT operatør
Denne operator bruges til at rotere udtryk med tabelværdi. Det blev først introduceret i SQL Server 2005-versionen. Det konverterer data fra rækker til kolonner. Den opdeler de unikke værdier fra én kolonne i mange kolonner og samler derefter de resterende kolonneværdier, der kræves i det endelige resultat.
Vi skal følge følgende trin for at oprette en PIVOT-tabel:
- Vælg basisdatasættet til pivotering.
- Opret midlertidige resultater ved hjælp af en afledt tabel eller CTE (common table expression).
- Gør brug af PIVOT-operatøren.
Syntaks
Følgende syntaks illustrerer brugen af PIVOT i SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Hvis vi bryder dette script, kan vi se, at det har to separate sektioner. Den første sektion vælger data fra hovedtabellen, og den anden sektion bestemmer, hvordan PIVOT-tabellen vil blive opbygget. Den anden del indeholder også nogle specielle nøgleord som SUM, FOR og IN. Lad os se betydningen af disse nøgleord i PIVOT-operatoren.
SUM
Denne operatør er vant til samle værdierne fra den angivne kolonne, der skal bruges i PIVOT-tabellen. Vi skal bruge det med PIVOT-operatoren for at få de aggregerede kolonnevisninger for værdisektionerne.
FOR Nøgleord
eksempel binært søgetræ
Dette nøgleord bruges til PIVOT-tabelsætningen til instruer PIVOT-operatøren på hvilken kolonne PIVOT-funktionen skal anvendes. Grundlæggende angiver det kolonnenavnene, der vil transformere fra rækker til kolonner.
IN søgeord
Dette søgeord viser alle de unikke værdier fra PIVOT-kolonnen for at blive vist som kolonnerne i PIVOT-tabellen.
Eksempel
Lad os forstå det ved hjælp af forskellige eksempler.
1. Den følgende sætning vælger først kolonnerne År, Nord og Syd som basisdata for pivotering. Opret derefter et midlertidigt resultat ved hjælp af den afledte tabel og anvend til sidst PIVOT-operatoren til at generere det endelige output. Dette output er også bestilt i det stigende år.
java concat strenge
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
Udførelse af denne erklæring vil producere nedenstående output. Her kan vi se beregnet sum af Nord- og Sydregionens salg svarende til årsværdierne .
2. Dette er et andet eksempel, hvor vi skal beregne summen af salg for hvert år svarende til regionsværdierne:
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;
Eksekvering af denne erklæring vil producere en fejl fordi vi ikke kan angive den numeriske værdi som et kolonnenavn direkte.
SQL Server giver os dog mulighed for at undgå dette problem ved at bruge parenteserne før hver heltalsværdi. Den opdaterede sætning vises i følgende kodestykke:
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 erklæring udført med succes og viser den beregnede sum af salg for hvert år svarende til regionsværdierne:
3. Det forrige eksempel på at få en PIVOT-tabel er nyttig, når vi er opmærksomme på alle mulige PIVOT-kolonneværdier. Men antag, at antallet af kolonner øges i det kommende år. I betragtning af det foregående eksempel har vi 2010, 2011 og 2012 år som PIVOT-kolonner. Der er dog ingen garanti for, at disse kolonner ikke vil ændre sig i fremtiden. Hvad sker der, hvis vi har data fra 2013 eller 2014, eller måske endnu mere? I sådanne tilfælde bliver vi nødt til at bruge dynamisk PIVOT bord forespørgsler for at løse dette problem.
Den dynamiske PIVOT-tabelforespørgsel indkapsler hele PIVOT-scriptet i en lagret procedure. Denne procedure vil give justerbare muligheder, hvilket giver os mulighed for at ændre vores krav ved at ændre nogle få parameterværdier.
Den følgende SQL-kode forklarer, hvordan den dynamiske PIVOT-tabel fungerer. I dette script har vi først hentet alle distinkte værdier fra PIVOT-kolonnen og derefter skrevet en SQL-sætning til udførelse med PIVOT-forespørgslen under kørsel. Lad os se outputtet efter at have udført dette script:
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 script har vi oprettet to parameteriserede variabler. Dens beskrivelse er givet nedenfor:
@PivotColumn : Denne variabel tager kolonnens navn fra den originale tabel, hvor PIVOT-tabellen er oprettet. For eksempel , her viser kolonnen 'Region' alle de tilgængelige regioner i kolonnerne.
@PivotList : Denne variabel tager kolonnelisten, som vi ønsker at vise som en outputkolonne i PIVOT-tabellen.
Udførelse af Dynamic Stored Procedure
Efter den vellykkede oprettelse af den dynamiske lagrede procedure er vi klar til at udføre den. Følgende sætning bruges til at kalde den dynamiske lagrede procedure for at vise PIVOT-tabellen under kørsel:
python slange vs anaconda
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Her har vi nu angivet kolonnenavnet ' Område ' som den første parameter og PIVOT kolonnelisten som den anden parameter. Udførelse af scriptet vil vise følgende output:
Nu kan vi tilføje flere kolonner i fremtiden under kørsel for at vise PIVOT-tabellen, hvilket ikke er muligt med de første to eksempler.
UNPIVOT operatør
Det er den omvendte metode af PIVOT-operatøren i SQL Server. Det bemander denne operatør udfører modsat drift af PIVOT ved at konvertere data fra kolonner til rækker. UNPIVOT-operatoren roterer også PIVOT-tabellen til den almindelige tabel. Det blev først introduceret i SQL Server 2005-versionen.
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
Lad os forstå, hvordan man UNPIVOT PIVOT-operationen med eksempler. Vi vil først oprette en originalt bord og PIVOT bord og derefter anvendt UNPIVOT-operator på denne tabel.
Følgende kodestykke erklærer først en midlertidig tabelvariabel @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Dernæst vil vi indsætte værdier i denne tabel 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
Nu kan vi udføre UNPIVOT-operation ved at bruge nedenstående erklæring:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Udførelse af kodestykket vil returnere følgende output:
Nedenstående kodestykke er et andet eksempel til først at udføre PIVOT-operation og derefter UNPIVOT-operation på den samme tabel inden for en enkelt forespørgsel:
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
Udførelse af kodestykket vil vise det samme output:
forskel mellem binært træ og binært søgetræ
BEMÆRK: UNPIVOT-processen er en omvendt operation af PIVOT-proceduren, men det er ikke en nøjagtig vending. Fordi rækker er blevet flettet, når PIVOT beregner aggregatet og kombinerer mange rækker i en enkelt række i resultatet, kan UNPIVOT-operationen derfor ikke få tabellen til at ligne originalen. Men hvis PIVOT-operatoren ikke flette mange rækker til en enkelt række, så kan UNPIVOT-operatoren få den originale tabel fra PIVOT-outputtet.
Konklusion
Denne artikel vil give et komplet overblik over PIVOT- og UNPIVOT-operatorer i SQL Server og konvertere et tabeludtryk til et andet. Den bør aldrig glemme, at UNPIVOT er den omvendte drift af PIVOT, men det er ikke den nøjagtige omvendte funktion af PIVOT-resultatet.