Dit artikel geeft een compleet overzicht van het gebruik van de PIVOT- en UNPIVOT-operatoren in SQL Server. De operatoren PIVOT en UNPIVOT zijn vergelijkbaar met de relationele operatoren die dit mogelijk maken het transformeren van de tabelwaarde-expressie naar een andere tabel . Beide operators genereren multidimensionale rapportage die helpt om snel een grote hoeveelheid gegevens te combineren en te vergelijken.
Wij kunnen gebruik maken van de PIVOT-operator wanneer we tabelwaarde-expressies moeten transformeren. Het splitst de unieke waarden van één kolom naar vele kolommen in het eindresultaat. Het ook aggregaten de resterende kolomwaarden die vereist zijn in het eindresultaat. UNPIVOT-operator converteert gegevens uit kolommen van een tabelwaarde-expressie naar kolomwaarden, wat het omgekeerde is van PIVOT.
Laten we het begrijpen met behulp van het eenvoudige diagram hieronder:
string naar geheel getal in Java
Aan de linkerkant van deze figuur zien we de originele dataset , die drie kolommen heeft: Jaar, Regio, En verkoop . Vervolgens zien we aan de rechterkant de PIVOT-tabel, die is opgebouwd door het draaien van de Regio (rijen) naar Noord en Zuid (kolommen) . Nadat we rijen in kolommen hebben omgezet, kunnen we een aggregatie van kolomwaarden Verkoop voor elk snijpunt tussen de kolommen en rijen van de PIVOT-tabel.
Laten we eerst een tabel maken met de naam draai_demo om de PIVOT- en UNPIVOT-operatoren te demonstreren. Met de volgende instructie wordt een nieuwe tabel gemaakt in onze opgegeven database:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Voer vervolgens enkele gegevens in deze tabel in, zoals hieronder:
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);
We kunnen de gegevens verifiëren met behulp van de SELECT-instructie. We krijgen de onderstaande uitvoer:
PIVOT-operator
Deze operator wordt gebruikt om expressies met tabelwaarden te roteren. Het werd voor het eerst geïntroduceerd in de SQL Server 2005-versie. Het converteert gegevens van rijen naar kolommen. Het splitst de unieke waarden van één kolom in vele kolommen en voegt vervolgens de resterende kolomwaarden samen die nodig zijn in het eindresultaat.
We moeten de volgende stappen volgen om een PIVOT-tabel te maken:
- Selecteer de basisgegevensset voor draaien.
- Creëer tijdelijke resultaten met behulp van een afgeleide tabel of CTE (common table expression).
- Maak gebruik van de PIVOT-operator.
Syntaxis
De volgende syntaxis illustreert het gebruik van PIVOT in SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Als we dit script breken, kunnen we zien dat het twee afzonderlijke secties heeft. De eerste sectie selecteert gegevens uit de hoofdtabel en de tweede sectie bepaalt hoe de PIVOT-tabel zal worden opgebouwd. Het tweede deel bevat ook enkele speciale trefwoorden zoals SUM, FOR en IN. Laten we de betekenis van deze trefwoorden bekijken in de PIVOT-operator.
SOM
Deze operator is eraan gewend de waarden samenvoegen uit de opgegeven kolom die moet worden gebruikt in de PIVOT-tabel. We moeten het gebruiken met de PIVOT-operator om de geaggregeerde kolomweergaven voor de waardensecties te krijgen.
VOOR trefwoord
Dit sleutelwoord wordt gebruikt voor de PIVOT-tabelinstructie instrueer de PIVOT-operator op welke kolom de PIVOT-functie moet worden toegepast. Kortom, het geeft de kolomnamen aan die van rijen naar kolommen zullen transformeren.
IN-trefwoord
Dit trefwoord vermeldt alle unieke waarden uit de PIVOT-kolom die moet worden weergegeven als de kolommen van de PIVOT-tabel.
Voorbeeld
Laten we het begrijpen met behulp van verschillende voorbeelden.
1. De volgende instructie selecteert eerst de kolommen Jaar, Noord en Zuid als basisgegevens voor het draaien. Maak vervolgens een tijdelijk resultaat met behulp van de afgeleide tabel en pas ten slotte de PIVOT-operator toe om de uiteindelijke uitvoer te genereren. Deze uitvoer wordt ook in het oplopende jaar geordend.
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
Het uitvoeren van deze instructie levert de onderstaande uitvoer op. Hier kunnen we de berekende som van verkopen uit de regio Noord en Zuid die overeenkomen met de jaarwaarden .
2. Dit is nog een voorbeeld waarin we de som van de verkopen voor elk jaar gaan berekenen die overeenkomt met de regiowaarden:
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;
Het uitvoeren van deze verklaring zal een fout opleveren omdat we de numerieke waarde niet rechtstreeks als kolomnaam kunnen opgeven.
Met SQL Server kunnen we dit probleem echter vermijden door de haakjes vóór elke gehele waarde te gebruiken. De bijgewerkte verklaring wordt weergegeven in het volgende codefragment:
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;
Deze verklaring is met succes uitgevoerd en geeft de berekende som van de verkopen weer voor elk jaar dat overeenkomt met de regiowaarden:
3. Het vorige voorbeeld van het verkrijgen van een PIVOT-tabel is handig als we op de hoogte zijn van alle mogelijke PIVOT-kolomwaarden. Maar stel dat het komende jaar het aantal kolommen wordt vergroot. Als we het voorgaande voorbeeld bekijken, hebben we de jaren 2010, 2011 en 2012 als PIVOT-kolommen. Er is echter geen garantie dat deze kolommen in de toekomst niet zullen veranderen. Wat gebeurt er als we gegevens hebben uit 2013 of 2014, of misschien zelfs meer? In dergelijke gevallen zullen we moeten gebruiken dynamische PIVOT-tabel vragen om dit probleem op te lossen.
De dynamische PIVOT-tabelquery kapselt het volledige PIVOT-script in een opgeslagen procedure in. Deze procedure biedt aanpasbare opties, waardoor we onze vereisten kunnen aanpassen door een paar geparametriseerde waarden te wijzigen.
De volgende SQL-code legt de werking van de dynamische PIVOT-tabel uit. In dit script hebben we eerst alle afzonderlijke waarden uit de PIVOT-kolom opgehaald en vervolgens een SQL-instructie geschreven voor uitvoering met de PIVOT-query tijdens runtime. Laten we de uitvoer bekijken na het uitvoeren van dit 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
In dit script hebben we twee geparametriseerde variabelen gemaakt. De beschrijving ervan vindt u hieronder:
@PivotColumn : deze variabele neemt de naam van de kolom over van de oorspronkelijke tabel waarin de draaitabel is gemaakt. Bijvoorbeeld Hier geeft de kolom 'Regio' alle regio's weer die beschikbaar zijn in de kolommen.
np.samenvoegen
@PivotLijst : Deze variabele neemt de kolomlijst die we willen weergeven als uitvoerkolom in de PIVOT-tabel.
Uitvoering van dynamische opgeslagen procedure
Na de succesvolle creatie van de dynamische opgeslagen procedure zijn we klaar om deze uit te voeren. De volgende instructie wordt gebruikt om de dynamische opgeslagen procedure aan te roepen om de PIVOT-tabel tijdens runtime weer te geven:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Hier hebben we nu de kolomnaam ' Regio ' als de eerste parameter en de PIVOT-kolomlijst als de tweede parameter. Als u het script uitvoert, wordt de volgende uitvoer weergegeven:
Nu kunnen we in de toekomst tijdens runtime meer kolommen toevoegen om de PIVOT-tabel weer te geven, wat niet mogelijk is met de eerste twee voorbeelden.
UNPIVOT-operator
Het is de omgekeerde methode van de PIVOT-operator in SQL Server. Het bemant deze operator voert de tegenovergestelde werking van PIVOT door gegevens van kolommen naar rijen te converteren. De UNPIVOT-operator roteert de PIVOT-tabel ook naar de gewone tabel. Het werd voor het eerst geïntroduceerd in de SQL Server 2005-versie.
Syntaxis
De volgende syntaxis illustreert de UNPIVOT in SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Voorbeeld
Laten we met voorbeelden begrijpen hoe we de PIVOT-bewerking kunnen ONTWIKKELEN. We zullen eerst een originele tafel en PIVOT tafel en vervolgens de UNPIVOT-operator op deze tabel toegepast.
Het volgende codefragment declareert eerst een tijdelijke tabelvariabele @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Vervolgens voegen we waarden in deze tabel in, zoals hieronder:
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 kunnen we de UNPIVOT-bewerking uitvoeren met behulp van de onderstaande verklaring:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Als u het codefragment uitvoert, wordt de volgende uitvoer geretourneerd:
Het onderstaande codefragment is een ander voorbeeld waarbij eerst de PIVOT-bewerking wordt uitgevoerd en vervolgens de UNPIVOT-bewerking in dezelfde tabel binnen één query:
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
Als u het codefragment uitvoert, wordt dezelfde uitvoer weergegeven:
OPMERKING: Het UNPIVOT-proces is een omgekeerde bewerking van de PIVOT-procedure, maar het is geen exacte omkering. Omdat rijen zijn samengevoegd wanneer PIVOT de aggregatie berekent en veel rijen in één enkele rij in het resultaat zijn gecombineerd, kan de UNPIVOT-bewerking de tabel niet zo maken als het origineel. Als de PIVOT-operator echter niet veel rijen samenvoegt tot één enkele rij, kan de UNPIVOT-operator de originele tabel uit de PIVOT-uitvoer halen.
Conclusie
Dit artikel geeft een compleet overzicht van PIVOT- en UNPIVOT-operatoren in SQL Server en converteert een tabelexpressie naar een andere. We mogen nooit vergeten dat UNPIVOT de omgekeerde werking van PIVOT is, maar niet precies het omgekeerde van het PIVOT-resultaat.