FROM – A PIVOT és a UNPIVOT használata

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példányAzure Synapse AnalyticsElemzési platformrendszer (PDW)SQL Analytics-végpont a Microsoft FabricbenRaktár a Microsoft FabricbenSQL-adatbázis a Microsoft Fabricben

A PIVOTUNPIVOT relációs operátorokkal egy táblaértékű kifejezést másik táblává alakíthat. PIVOT egy táblaértékű kifejezés elforgatásával a kifejezés egyik oszlopának egyedi értékeit több oszlopra alakítja a kimenetben. PIVOT emellett olyan összesítéseket is futtat, amelyekben a végső kimenetben lévő összes többi oszlopértékre szükség van. UNPIVOT a táblázatértékelt kifejezés oszlopainak oszlopértékekké való elforgatásával az ellenkező műveletet PIVOThajtja végre.

A szintaxis PIVOT egyszerűbb és olvashatóbb, mint az összetett utasítások sorozatában SELECT...CASE egyébként megadható szintaxis. A szintaxis PIVOTteljes leírásáért lásd a FROM záradékot.

Note

Egy T-SQL-utasítás ismételt használata PIVOT/UNPIVOT negatívan befolyásolhatja a lekérdezés teljesítményét.

A cikkben szereplő kódminták a AdventureWorks2025 vagy AdventureWorksDW2025 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

Syntax

Ez a szakasz az operátor és PIVOT az UNPIVOT operátor használatát foglalja össze.

Az operátor szintaxisa PIVOT .

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

Az operátor szintaxisa UNPIVOT .

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

  • A UNPIVOT záradék oszlopazonosítói a katalógus rendezést követik.

    • Az Azure SQL Database esetében a rendezés mindig SQL_Latin1_General_CP1_CI_ASaz .

    • A részben tartalmazott SQL Server-adatbázisok esetén a rendezés mindig Latin1_General_100_CI_AS_KS_WS_SC.

  • Ha az oszlop más oszlopokkal van kombinálva, az ütközések elkerülése érdekében egy rendezési záradékra (COLLATE DATABASE_DEFAULT) van szükség.

  • A Microsoft Fabric és az Azure Synapse Analytics-készletekben az operátorral rendelkező PIVOT lekérdezések meghiúsulnak, ha a nempivot oszlop kimenete a következő GROUP BYszerint történikPIVOT: . Áthidaló megoldásként távolítsa el a nempivot oszlopot a GROUP BY. A lekérdezés eredményei megegyeznek, mivel ez GROUP BY a záradék duplikált.

  • Az oszlopnevek sysname vagy nvarchar(128) típusúak. Mivel UNPIVOT a projektek oszlopneveit értékként adja meg, az oszlop adattípusa UNPIVOT szintén nvarchar(128) lesz, amely nem támogatott adattípus a Fabric Data Warehouse-ban. Ha a Fabric egyik raktárában lévő táblába szeretné menteni az eredményeket UNPIVOT , a Fabric Data Warehouse egyik támogatott adattípusára adhatja át. Például:

    CREATE TABLE myTable AS
SELECT value,
       CAST(columnNames as VARCHAR(128) ) columnNames
FROM myTableToUnpivot
UNPIVOT( value
        FOR columnNames IN( col1, col2 )) unpvt;

Példa alapszintű PIVOT-ra

Az alábbi példakód egy kétoszlopos táblát hoz létre, amely négy sorból áll.

USE AdventureWorks2022;
GO

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

Itt van az eredmények összessége.

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

Nincs meghatározva olyan termék, amelynek értéke 3 a következő DaysToManufacture.

Az alábbi kód ugyanazt az eredményt jeleníti meg, úgy, hogy az DaysToManufacture értékek oszlopfejlécekké váljanak. Egy oszlop három ([3]) napig van megadva, annak ellenére, hogy az eredmények .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;

Itt van az eredmények összessége.

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

Összetett PIVOT-példa

Gyakori eset, amikor PIVOT hasznos lehet, ha kereszttáblázási jelentéseket szeretne létrehozni az adatok összegzéséhez. Tegyük fel például, hogy le szeretné kérdezni a PurchaseOrderHeader mintaadatbázis AdventureWorks2025 tábláját az egyes alkalmazottak által leadott beszerzési rendelések számának meghatározásához. Az alábbi lekérdezés a szállító által megrendelt jelentést tartalmazza.

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;

Íme egy részleges eredményhalmaz.

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

Az alkijelölési utasítás által visszaadott eredményeket a rendszer az EmployeeID oszlopon forgatja.

SELECT PurchaseOrderID,
    EmployeeID,
    VendorID
FROM PurchaseOrderHeader;

Az oszlop által EmployeeID visszaadott egyedi értékek mezővé válnak a végső eredményhalmazban. Ezért a kimutatás záradékában minden számnak EmployeeID van egy oszlopa, amely az alkalmazottak 250, 251és 256257260 ebben a példában szerepel. Az PurchaseOrderID oszlop értékoszlopként szolgál, amely alapján a végső kimenetben visszaadott oszlopok, amelyeket csoportosítási oszlopoknak neveznek, csoportosítva vannak. Ebben az esetben a csoportosítási oszlopokat a COUNT függvény összesíti. Megjelenik egy figyelmeztető üzenet, amely azt jelzi, hogy az PurchaseOrderID oszlopban megjelenő null értékek nem lettek figyelembe véve az COUNT egyes alkalmazottak számítása során.

Important

Ha összesítő függvényeket PIVOThasznál, az értékoszlopban lévő null értékek jelenléte nem lesz figyelembe véve az összesítés kiszámításakor.

UNPIVOT példa

UNPIVOT az oszlopok sorba forgatásával szinte fordított műveletet PIVOThajt végre. Tegyük fel, hogy az előző példában létrehozott tábla az adatbázisban pvtvan tárolva, és el szeretné forgatni az oszlopazonosítókat Emp1, Emp2, , Emp3Emp4és Emp5 egy adott szállítónak megfelelő sorértékekké. Ezért két további oszlopot kell azonosítania.

A forgó oszlopértékeket tartalmazó oszlopot (Emp1stb.) a függvény meghívjaEmp2, Employeeés az az oszlop, amely az elforgatott oszlopok alatt jelenleg meglévő értékeket tartalmazza, az úgynevezett Orders. Ezek az oszlopok a Transact-SQL definíciójának pivot_column és value_column felelnek meg. Itt van a lekérdezés.

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

Íme egy részleges eredményhalmaz.

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 nem a pontos fordítottja PIVOT. PIVOT összesítést végez, és a kimenet egy sorába egyesít egy lehetséges több sort. UNPIVOT nem reprodukálja az eredeti táblaértékelt kifejezés eredményét, mert a sorok egyesítve lettek. NULL Emellett a bemenet UNPIVOT értékei eltűnnek a kimenetben. Ha az értékek eltűnnek, az azt mutatja, hogy a művelet előtt előfordulhattak eredeti NULL értékek a PIVOT bemenetben.

A Sales.vSalesPersonSalesByFiscalYears mintaadatbázis nézete az AdventureWorks2025 egyes értékesítők összes értékesítését adja PIVOT vissza minden pénzügyi évre vonatkozóan. Ha az SQL Server Management Studióban szeretné szkriptbe adni a nézetet, az Object Explorerben keresse meg a nézetet az adatbázis AdventureWorks2025 mappájában. Kattintson a jobb gombbal a nézet nevére, majd válassza a Szkriptnézet parancsot.

Kapcsolódó tartalom

  • Last updated on