FROM - PIVOT en UNPIVOT gebruiken

2025-07-29

Van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)SQL Analytics-eindpunt in Microsoft Fabric Magazijn in Microsoft Fabric SQL-database in Microsoft Fabric Preview

U kunt de PIVOT en UNPIVOT relationele operatoren gebruiken om een expressie met tabelwaarden te wijzigen in een andere tabel. PIVOT draait een expressie met tabelwaarden door de unieke waarden van één kolom in de expressie om te zetten in meerdere kolommen in de uitvoer. PIVOT voert ook aggregaties uit waarbij ze vereist zijn voor eventuele resterende kolomwaarden die in de uiteindelijke uitvoer moeten worden gebruikt. UNPIVOT voert de tegenovergestelde bewerking PIVOTuit door kolommen van een tabelwaarde-expressie te roteren in kolomwaarden.

De syntaxis voor PIVOT is gemakkelijker en beter leesbaar dan de syntaxis die anders kan worden opgegeven in een complexe reeks SELECT...CASE instructies. Zie de FROM-component voor een volledige beschrijving van de syntaxis voorPIVOT.

Note

Herhaald gebruik van PIVOT/UNPIVOT binnen één T-SQL-instructie kan een negatieve invloed hebben op de queryprestaties.

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase die u kunt downloaden van de startpagina van Microsoft SQL Server Samples en Community Projects .

Syntax

In deze sectie wordt uitgelegd hoe u de PIVOT en UNPIVOT operator gebruikt.

Syntaxis voor de PIVOT operator.

SELECT [ <non-pivoted column> [ AS <column name> ] , ]
    ...
    [ <first pivoted column> [ AS <column name> ] ,
    [ <second pivoted column> [ AS <column name> ] , ]
    ...
    [ <last pivoted column> [ AS <column name> ] ] ]
FROM
    ( <SELECT query that produces the data> )
    AS <alias for the source query>
PIVOT
(
    <aggregation function> ( <column being aggregated> )
FOR <column that contains the values that become column headers>
    IN ( <first pivoted column>
         , <second pivoted column>
         , ... <last pivoted column> )
) AS <alias for the pivot table>
[ <optional ORDER BY clause> ]
[ ; ]

Syntaxis voor de UNPIVOT operator.

SELECT [ <non-pivoted column> [ AS <column name> ] , ]
    ...
    [ <output column for names of the pivot columns> [ AS <column name> ] , ]
    [ <new output column created for values in result of the source query> [ AS <column name> ] ]
FROM
    ( <SELECT query that produces the data> )
    AS <alias for the source query>
UNPIVOT
(
    <new output column created for values in result of the source query>
FOR <output column for names of the pivot columns>
    IN ( <first pivoted column>
         , <second pivoted column>
         , ... <last pivoted column> )
)
[ <optional ORDER BY clause> ]
[ ; ]

Remarks

De kolom-id's in de UNPIVOT-component volgen de catalogussortering.

Voor Azure SQL Database is de sortering altijd SQL_Latin1_General_CP1_CI_AS.
Voor gedeeltelijk ingesloten SQL Server-databases wordt de sortering altijd Latin1_General_100_CI_AS_KS_WS_SC.

Als de kolom wordt gecombineerd met andere kolommen, is een samenvoegingscomponent (COLLATE DATABASE_DEFAULT) vereist om conflicten te voorkomen.

In Microsoft Fabric- en Azure Synapse Analytics-pools mislukken query's met PIVOT operator als de GROUP BY uitvoer van de niet-draaitabelkolom wordt weergegeven door PIVOT. Als tijdelijke oplossing verwijdert u de niet-draaitabelkolom uit de GROUP BY. Queryresultaten zijn hetzelfde, omdat deze GROUP BY component een duplicaat is.

Basis-PIVOT-voorbeeld

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

USE AdventureWorks2022;
GO

SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;

Hier is het resultatenoverzicht.

DaysToManufacture  AverageCost
------------------ ------------
0                  5.0885
1                  223.88
2                  359.1082
4                  949.4105

Er worden geen producten gedefinieerd met de waarde 3 voor DaysToManufacture.

In de volgende code wordt hetzelfde resultaat weergegeven, zodat de DaysToManufacture waarden de kolomkoppen worden. Er wordt drie ([3]) dagen een kolom opgegeven, ook al zijn NULLde resultaten wel .

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS CostSortedByProductionDays,
    [0], [1], [2], [3], [4]
FROM (
    SELECT DaysToManufacture,
        StandardCost
    FROM Production.Product
) AS SourceTable
PIVOT (
    AVG(StandardCost) FOR DaysToManufacture IN
    ([0], [1], [2], [3], [4])
) AS PivotTable;

Hier is het resultatenoverzicht.

CostSortedByProductionDays  0           1           2           3           4
--------------------------- ----------- ----------- ----------- ----------- -----------
AverageCost                 5.0885      223.88      359.1082    NULL        949.4105

Voorbeeld van complexe draaigrafiek

Een veelvoorkomend scenario waarbij PIVOT dit handig kan zijn, is wanneer u kruistabelrapporten wilt genereren om een samenvatting van de gegevens te geven. Stel dat u een query wilt uitvoeren op de PurchaseOrderHeader tabel in de AdventureWorks2022 voorbeelddatabase om het aantal inkooporders te bepalen dat bepaalde werknemers hebben geplaatst. De volgende query bevat dit rapport, gesorteerd door de leverancier.

USE AdventureWorks2022;
GO

SELECT VendorID,
    [250] AS Emp1,
    [251] AS Emp2,
    [256] AS Emp3,
    [257] AS Emp4,
    [260] AS Emp5
FROM
(
    SELECT PurchaseOrderID,
    EmployeeID, VendorID
    FROM Purchasing.PurchaseOrderHeader
) p
PIVOT
(
    COUNT (PurchaseOrderID)
    FOR EmployeeID IN ([250], [251], [256], [257], [260])
) AS pvt
ORDER BY pvt.VendorID;

Hier volgt een gedeeltelijke resultatenset.

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
----------- ----------- ----------- ----------- ----------- -----------
1492        2           5           4           4           4
1494        2           5           4           5           4
1496        2           4           4           5           5
1498        2           5           4           4           4
1500        3           4           4           5           4

De resultaten die door deze subselectie-instructie worden geretourneerd, worden in de EmployeeID kolom gedraaid.

SELECT PurchaseOrderID,
    EmployeeID,
    VendorID
FROM PurchaseOrderHeader;

De unieke waarden die door de EmployeeID kolom worden geretourneerd, worden velden in de uiteindelijke resultatenset. Als zodanig is er een kolom voor elk EmployeeID getal dat is opgegeven in de draaicomponent, die werknemers250, 251, , 256en 257260 in dit voorbeeld. De PurchaseOrderID kolom fungeert als de waardekolom, waarmee de kolommen die worden geretourneerd in de uiteindelijke uitvoer, die de groeperingskolommen worden genoemd, worden gegroepeerd. In dit geval worden de groeperingskolommen samengevoegd door de COUNT functie. Er wordt een waarschuwingsbericht weergegeven dat aangeeft dat null-waarden die in de PurchaseOrderID kolom worden weergegeven, niet werden overwogen bij het berekenen van de COUNT waarden voor elke werknemer.

Important

Wanneer statistische functies worden gebruikt, PIVOTwordt de aanwezigheid van null-waarden in de waardekolom niet meegenomen bij het berekenen van een aggregatie.

UNPIVOT example

UNPIVOT voert bijna de omgekeerde bewerking van PIVOT, door kolommen in rijen te draaien. Stel dat de tabel die in het vorige voorbeeld is geproduceerd, is opgeslagen in de database als pvt, en u de kolom-id's Emp1, Emp2, Emp3en Emp4Emp5 in rijwaarden wilt draaien die overeenkomen met een bepaalde leverancier. Daarom moet u twee extra kolommen identificeren.

De kolom die de kolomwaarden bevat die u draait (Emp1, enzovoort) wordt aangeroepen en de kolom met de waarden die momenteel bestaan onder de kolommen die worden gedraaid, wordt aangeroepenEmployeeOrders. Emp2 Deze kolommen komen overeen met respectievelijk de pivot_column en value_column in de definitie van Transact-SQL. Dit is de query.

-- Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (
    VendorID INT,
    Emp1 INT,
    Emp2 INT,
    Emp3 INT,
    Emp4 INT,
    Emp5 INT);
GO

INSERT INTO pvt
VALUES (1, 4, 3, 5, 4, 4);

INSERT INTO pvt
VALUES (2, 4, 1, 5, 5, 5);

INSERT INTO pvt
VALUES (3, 4, 3, 5, 4, 4);

INSERT INTO pvt
VALUES (4, 4, 2, 5, 5, 4);

INSERT INTO pvt
VALUES (5, 5, 1, 5, 5, 5);
GO

-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM (
    SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM pvt
) p
UNPIVOT
(
    Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)
) AS unpvt;
GO

Hier volgt een gedeeltelijke resultatenset.

VendorID    Employee    Orders
----------- ----------- ------
1            Emp1       4
1            Emp2       3
1            Emp3       5
1            Emp4       4
1            Emp5       4
2            Emp1       4
2            Emp2       1
2            Emp3       5
2            Emp4       5
2            Emp5       5

UNPIVOT is niet het exacte omgekeerde van PIVOT. PIVOT voert een aggregatie uit en voegt mogelijk meerdere rijen samen in één rij in de uitvoer. UNPIVOT reproduceert het oorspronkelijke expressieresultaat met tabelwaarden niet, omdat rijen zijn samengevoegd. NULL Waarden in de invoer van UNPIVOT verdwijnen ook in de uitvoer. Wanneer de waarden verdwijnen, ziet u dat er vóór de bewerking mogelijk oorspronkelijke NULL waarden in de PIVOT invoer zijn.

De Sales.vSalesPersonSalesByFiscalYears weergave in de AdventureWorks2022 voorbeelddatabase gebruikt PIVOT voor het retourneren van de totale verkoop voor elke verkoper, voor elk fiscaal jaar. Als u de weergave in SQL Server Management Studio wilt uitvoeren, zoekt u in Objectverkenner de weergave onder de map Weergaven voor de AdventureWorks2022 database. Klik met de rechtermuisknop op de weergavenaam en selecteer vervolgens Scriptweergave als.

Feedback

Is deze pagina nuttig?