FROM — używanie elementu PIVOT i UNPIVOT

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Punkt końcowy analizy SQL w usłudze Microsoft Fabric Hurtownia danych w usłudze Microsoft Fabric Baza danych SQL w usłudze Microsoft Fabric

Operatory i PIVOT relacyjne umożliwiają UNPIVOT zmianę wyrażenia wartości tabeli na inną tabelę. PIVOT Obraca wyrażenie wartości tabeli, zamieniając unikatowe wartości z jednej kolumny w wyrażeniu na wiele kolumn w danych wyjściowych. PIVOT Uruchamia również agregacje, w których są one wymagane dla pozostałych wartości kolumn, które są wymagane w końcowych danych wyjściowych. UNPIVOT wykonuje odwrotną operację na PIVOT, obracając kolumny wyrażenia wartości tabeli w wartości kolumny.

Składnia jest PIVOT łatwiejsza i bardziej czytelna niż składnia, która w przeciwnym razie może być określona w złożonej serii instrukcji SELECT...CASE . Pełny opis składni dla PIVOTprogramu można znaleźć w klauzuli FROM.

Note

Wielokrotne użycie PIVOT/UNPIVOT w ramach pojedynczej instrukcji języka T-SQL może negatywnie wpłynąć na wydajność zapytań.

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2025 lub AdventureWorksDW2025, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

Syntax

W tej sekcji podsumowano sposób używania PIVOT operatora i UNPIVOT .

Składnia PIVOT operatora.

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> ]
[ ; ]

Składnia UNPIVOT operatora.

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

Identyfikatory kolumn w klauzuli UNPIVOT są zgodne z sortowaniem wykazu.

W przypadku usługi Azure SQL Database sortowanie jest zawsze SQL_Latin1_General_CP1_CI_AS.
W przypadku częściowo zawartych baz danych programu SQL Server sortowanie jest zawsze Latin1_General_100_CI_AS_KS_WS_SC.

Jeśli kolumna jest połączona z innymi kolumnami, w celu uniknięcia konfliktów wymagana jest klauzula sortowania (COLLATE DATABASE_DEFAULT).

W pulach usług Microsoft Fabric i Azure Synapse Analytics zapytania z operatorem PIVOT kończą się niepowodzeniemGROUP BY, jeśli w danych wyjściowych kolumny niezwiązanej z przestawnym elementem jest .PIVOT Aby obejść ten problem, usuń kolumnę inną niż przestawna z tabeli GROUP BY. Wyniki zapytania są takie same, jak ta GROUP BY klauzula jest duplikatem.

Podstawowy przykład tabeli PRZESTAWnej

Poniższy przykład kodu tworzy tabelę dwukolumna zawierającą cztery wiersze.

USE AdventureWorks2022;
GO

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

Oto zestaw wyników.

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

Żadne produkty nie są zdefiniowane z wartością 3 dla DaysToManufactureelementu .

Poniższy kod wyświetla ten sam wynik, przestawny, aby DaysToManufacture wartości stały się nagłówkami kolumn. Kolumna jest udostępniana przez trzy ([3]) dni, mimo że wyniki to NULL.

-- 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;

Oto zestaw wyników.

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

Przykład złożonych tabeli PRZESTAWnej

Typowy scenariusz, w którym PIVOT może być przydatny, to generowanie raportów między tabulacji w celu wyświetlenia podsumowania danych. Załóżmy na przykład, że chcesz wysłać zapytanie PurchaseOrderHeader do tabeli w przykładowej AdventureWorks2025 bazie danych, aby określić liczbę zamówień zakupu złożonych przez niektórych pracowników. Poniższe zapytanie zawiera ten raport uporządkowany przez dostawcę.

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;

Oto zestaw wyników częściowych.

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

Wyniki zwrócone przez tę instrukcję podwybierz są przestawiane na kolumnie EmployeeID .

SELECT PurchaseOrderID,
    EmployeeID,
    VendorID
FROM PurchaseOrderHeader;

Unikatowe wartości zwracane przez kolumnę EmployeeID stają się polami w końcowym zestawie wyników. W związku z tym istnieje kolumna dla każdej EmployeeID liczby określonej w klauzuli przestawnej, która jest pracownikami 250, , 251256, , 257i 260 w tym przykładzie. Kolumna PurchaseOrderID służy jako kolumna wartości, względem której kolumny zwrócone w końcowych danych wyjściowych, nazywane kolumnami grupowania, są grupowane. W takim przypadku kolumny grupowania są agregowane przez COUNT funkcję . Zostanie wyświetlony komunikat ostrzegawczy wskazujący, że wszystkie wartości null wyświetlane w PurchaseOrderID kolumnie nie były brane pod uwagę podczas obliczania COUNT wartości dla każdego pracownika.

Important

Gdy funkcje agregujące są używane z usługą PIVOT, obecność żadnych wartości null w kolumnie wartości nie jest brana pod uwagę podczas obliczania agregacji.

Przykład UNPIVOT

UNPIVOT wykonuje niemal odwrotną operację PIVOTelementu , obracając kolumny w wiersze. Załóżmy, że tabela utworzona w poprzednim przykładzie jest przechowywana w bazie danych jako pvt, i chcesz obrócić identyfikatory kolumn , , Emp1Emp2, Emp3i Emp4 na wartości wierszyEmp5, które odpowiadają określonemu dostawcy. W związku z tym należy zidentyfikować dwie dodatkowe kolumny.

Kolumna zawierająca wartości kolumny, które są obracane (Emp1itdEmp2.), nosi nazwę Employee, a kolumna zawierająca wartości, które obecnie istnieją w obróconych kolumnach, jest nazywana .Orders Te kolumny odpowiadają odpowiednio pivot_column i value_column w definicji Transact-SQL. Oto zapytanie.

-- 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

Oto zestaw wyników częściowych.

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 nie jest dokładnym odwrotnym elementem PIVOT. PIVOT przeprowadza agregację i scala możliwe wiele wierszy w jednym wierszu w danych wyjściowych. UNPIVOT nie odtwarza oryginalnego wyniku wyrażenia wartości tabeli, ponieważ wiersze zostały scalone. NULL Ponadto wartości w danych wejściowych znikają w danych wyjściowychUNPIVOT. Gdy wartości znikną, pokazuje, że przed operacją mogły istnieć oryginalne NULL wartości w danych wejściowych PIVOT .

Widok Sales.vSalesPersonSalesByFiscalYears w przykładowej AdventureWorks2025 bazie danych używa PIVOT metody , aby zwrócić łączną sprzedaż dla każdego sprzedawcy w każdym roku obrachunkowym. Aby uruchomić skrypt w programie SQL Server Management Studio, w Eksploratorze obiektów znajdź widok w folderze AdventureWorks2025 dla bazy danych. Kliknij prawym przyciskiem myszy nazwę widoku, a następnie wybierz pozycję Widok skryptu jako.

Sprzężenie zwrotne

Czy ta strona była pomocna?

Last updated on 2025-11-18