FROM – PIVOT en UNPIVOT gebruiken

• 14/10/2019
• 5 minuten om te lezen
• • V
  • c
  • M
  • i
  • M
  • +7

Is van toepassing op: SQL Server (alle ondersteunde versies) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse

U kunt de relationele operators PIVOT en UNPIVOT gebruiken om verander een tabelwaarde-uitdrukking in een andere tabel. PIVOT roteert een uitdrukking met tabelwaarde door de unieke waarden van één kolom in de uitdrukking om te zetten in meerdere kolommen in de uitvoer. En PIVOT voert aggregaties uit waar ze “nodig zijn voor alle resterende kolomwaarden die in de uiteindelijke uitvoer gewenst zijn. UNPIVOT voert de tegenovergestelde bewerking uit naar PIVOT door kolommen van een tabelwaarde-expressie in kolomwaarden te roteren.

De syntaxis voor PIVOT is eenvoudiger en leesbaarder dan de syntaxis die anders gespecificeerd in een complexe reeks SELECT...CASE instructies. Voor een volledige beschrijving van de syntaxis voor PIVOT, zie FROM (Transact-SQL).

Syntaxis

De volgende syntaxis vat samen hoe de PIVOT operator te gebruiken.

Opmerkingen

De kolom-IDs in de UNPIVOT -clausule volgen de catalogussortering. Voor SQL Database is de sortering altijd SQL_Latin1_General_CP1_CI_AS. Voor SQL Server gedeeltelijk ingesloten databases, is de sortering altijd Latin1_General_100_CI_AS_KS_WS_SC. Als de kolom een combinatie is ed met andere kolommen, dan is een collate-clausule (COLLATE DATABASE_DEFAULT) vereist om conflicten te vermijden.

Basic PIVOT-voorbeeld

Het volgende codevoorbeeld produceert een tabel met twee kolommen met vier rijen.

Hier is de resultatenset.

Er zijn geen producten gedefinieerd met drie DaysToManufacture.

De volgende code geeft hetzelfde resultaat weer, gedraaid zodat de DaysToManufacture waarden worden de kolomkoppen. Er is een kolom voorzien voor drie dagen, ook al zijn de resultaten NULL.

Hier is de resultatenset .

Complex PIVOT-voorbeeld

Een veelvoorkomend scenario waarin PIVOT nuttig kan zijn, is wanneer u kruistabelrapporten wilt genereren om een samenvatting van de gegevens te geven. Stel dat u de PurchaseOrderHeader -tabel in de AdventureWorks2014 -voorbeelddatabase wilt doorzoeken om het aantal inkooporders te bepalen dat door bepaalde werknemers is geplaatst. De volgende zoekopdracht levert dit rapport op, gerangschikt op leverancier.

Hier is een gedeeltelijke resultaatset.

De resultaten die door deze subselectie worden geretourneerd instructie draait om de EmployeeID kolom.

De unieke waarden die worden geretourneerd door de EmployeeID kolom worden velden in de uiteindelijke resultatenset. Als zodanig is er “een kolom voor elk EmployeeID -getal gespecificeerd in de pivot-clausule: in dit geval werknemers 250, 251, 256, 257, en 260. De PurchaseOrderID -kolom dient als de waardekolom, waartegen de kolommen die in de uiteindelijke uitvoer worden geretourneerd, die de groeperingskolommen worden genoemd, worden gegroepeerd. In dit geval worden de groeperingskolommen geaggregeerd door de COUNT functie. Merk op dat er een waarschuwingsbericht verschijnt dat aangeeft dat alle null-waarden in de PurchaseOrderID -kolom “niet in aanmerking zijn genomen bij het berekenen van de COUNT voor elke werknemer.

UNPIVOT-voorbeeld

UNPIVOT voert bijna de omgekeerde bewerking uit van PIVOT, door kolommen in rijen te roteren. Stel dat de tabel die in het vorige voorbeeld is geproduceerd in de database is opgeslagen als pvt, en u wilt de kolom-IDs Emp1, Emp2, Emp3, Emp4, en Emp5 in rijwaarden die overeenkomen met een bepaalde leverancier. Als zodanig moet u twee extra kolommen identificeren.De kolom die de kolomwaarden bevat die u “roteert (Emp1, Emp2, …) wordt genoemd Employee, en de kolom die de waarden bevat die momenteel bestaan onder de kolommen die worden geroteerd, wordt Orders genoemd. Deze kolommen komen overeen met de pivot_column en value_column , respectievelijk in de Transact-SQL-definitie. Hier is de query.

Hier is een gedeeltelijke resultaatset.

Merk op dat UNPIVOT is niet “t precies het omgekeerde van PIVOT. PIVOT voert een aggregatie uit en voegt mogelijk meerdere rijen samen tot een enkele rij in de uitvoer. UNPIVOT reproduceert niet het oorspronkelijke expressieresultaat met tabelwaarde omdat rijen zijn samengevoegd. Ook verdwijnen null-waarden in de invoer van UNPIVOT in de uitvoer. Wanneer de waarden verdwijnen, toont dit aan dat er mogelijk originele null-waarden in de invoer waren vóór de PIVOT -bewerking.

De Sales.vSalesPersonSalesByFiscalYears -weergave in de AdventureWorks2012-voorbeelddatabase gebruikt PIVOT om de totale verkoop voor elke verkoper voor elk fiscaal jaar te retourneren. Om de weergave in SQL Server Management Studio te scripten , zoek in Objectverkenner de weergave onder de map Weergaven voor de AdventureWorks2012-database. Klik met de rechtermuisknop op de weergavenaam en selecteer Scriptweergave als.

Zie ook

FROM (Transact -SQL)
CASE (Transact-SQL)