Poznámka:
Přístup k této stránce vyžaduje autorizaci. Můžete se zkusit přihlásit nebo změnit adresáře.
Přístup k této stránce vyžaduje autorizaci. Můžete zkusit změnit adresáře.
Platí pro:
SQL ServerAzure SQL DatabaseSpravovaná instance Azure SQLAzure Synapse AnalyticsAnalytics Platform System (PDW)Koncový bod analýzy SQL v Microsoft FabricSklad v Microsoft FabricDatabáze SQL v Microsoft Fabric
Relační operátory PIVOTUNPIVOT můžete použít ke změně výrazu s hodnotou tabulky na jinou tabulku.
PIVOT otočí výraz s hodnotou tabulky tím, že změní jedinečné hodnoty z jednoho sloupce ve výrazu na více sloupců ve výstupu.
PIVOT spustí také agregace, kde jsou vyžadovány u všech zbývajících hodnot sloupců, které jsou v konečném výstupu požadovány.
UNPIVOT provádí opačnou operaci než PIVOT, otočením sloupců výrazu s hodnotou tabulky do hodnot sloupců.
Syntaxe je PIVOT srozumitelnější a čitelnější než syntaxe, která by jinak mohla být zadána v komplexní řadě SELECT...CASE příkazů. Úplný popis syntaxe pro PIVOT, viz klauzule FROM.
Note
Opakované použití PIVOT/UNPIVOT v rámci jednoho příkazu T-SQL může negativně ovlivnit výkon dotazů.
Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2025 nebo AdventureWorksDW2025, kterou si můžete stáhnout z domovské stránky Microsoft SQL Serveru pro ukázky a komunitní projekty .
Syntax
Tato část shrnuje, jak používat PIVOT operátor a UNPIVOT operátor.
Syntaxe operátoru 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> ]
[ ; ]
Syntaxe operátoru 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
Identifikátory sloupců v klauzuli UNPIVOT se řídí kolací katalogu.
Pro Azure SQL Database je kolace vždy
SQL_Latin1_General_CP1_CI_AS.U částečně obsažených databází SQL Serveru je kolace vždy
Latin1_General_100_CI_AS_KS_WS_SC.
Pokud se sloupec zkombinuje s jinými sloupci, vyžaduje se kompletovací klauzule (COLLATE DATABASE_DEFAULT), aby nedocházelo ke konfliktům.
Ve fondech Microsoft Fabric a Azure Synapse Analytics dotazy s operátorem PIVOT selžou GROUP BY , pokud se ve výstupu sloupce, který nepřekontuje, podle PIVOT. Jako alternativní řešení odeberte sloupec, který není v kontingenčním sloupci GROUP BY. Výsledky dotazu jsou stejné, jako je tato GROUP BY klauzule duplicitní.
Příklad základní kontingenční tabulky
Následující příklad kódu vytvoří tabulku se dvěma sloupci, která má čtyři řádky.
USE AdventureWorks2022;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Tady je soubor výsledků.
DaysToManufacture AverageCost
------------------ ------------
0 5.0885
1 223.88
2 359.1082
4 949.4105
Žádné produkty nejsou definovány s hodnotou 3 pro DaysToManufacture.
Následující kód zobrazí stejný výsledek, otočený tak, aby DaysToManufacture se hodnoty staly záhlavími sloupců. Sloupec je k dispozici po dobu tří ([3]) dnů, i když jsou NULLvýsledky .
-- 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;
Tady je soubor výsledků.
CostSortedByProductionDays 0 1 2 3 4
--------------------------- ----------- ----------- ----------- ----------- -----------
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Příklad komplexní kontingenční tabulky
Běžným scénářem, ve PIVOT kterém může být užitečné, je, když chcete vygenerovat křížové tabulkové sestavy, abyste získali souhrn dat. Předpokládejme například, že chcete zadat dotaz na PurchaseOrderHeader tabulku v AdventureWorks2025 ukázkové databázi, abyste zjistili počet nákupních objednávek zadaných určitými zaměstnanci. Následující dotaz poskytuje tuto sestavu seřazenou dodavatelem.
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;
Tady je částečná sada výsledků.
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
Výsledky vrácené tímto příkazem dílčího výběru jsou otočené ve sloupci EmployeeID .
SELECT PurchaseOrderID,
EmployeeID,
VendorID
FROM PurchaseOrderHeader;
Jedinečné hodnoty vrácené sloupcem EmployeeID se stanou poli v konečné sadě výsledků. V takovém případě je sloupec pro každé EmployeeID číslo zadané v kontingenční klauzuli, které jsou zaměstnanci 250, 251, 256, 257a 260 v tomto příkladu. Sloupec PurchaseOrderID slouží jako sloupec hodnoty, do kterého jsou sloupce vrácené v konečném výstupu, které se nazývají sloupce seskupování, seskupené. V tomto případě jsou sloupce seskupení agregovány COUNT funkcí. Zobrazí se zpráva s upozorněním, která značí, že při výpočtu jednotlivých zaměstnanců nebyly při výpočtu PurchaseOrderID hodnoty null ve COUNT sloupci považovány za nulové hodnoty.
Important
Při použití PIVOTagregačních funkcí se při výpočtu agregace nepovažují za přítomnost všech hodnot null ve sloupci hodnoty.
Příklad UNPIVOT
UNPIVOT provádí téměř obrácenou operaci PIVOT, otočením sloupců do řádků. Předpokládejme, že tabulka vytvořená v předchozím příkladu je uložena v databázi jako pvta chcete otočit identifikátory Emp1sloupců , Emp2, , Emp3Emp4a Emp5 do hodnot řádků, které odpovídají konkrétnímu dodavateli. Proto musíte identifikovat dva další sloupce.
Sloupec, který obsahuje hodnoty sloupců, které otočíte (Emp1Emp2atd.), se nazývá Employeea sloupec, který obsahuje hodnoty, které aktuálně existují pod otočenými sloupci, se nazývá Orders. Tyto sloupce odpovídají pivot_column a value_column v definici Transact-SQL. Tady je dotaz.
-- 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
Tady je částečná sada výsledků.
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 není přesným obrácením PIVOT.
PIVOT provede agregaci a sloučí možné více řádků do jednoho řádku ve výstupu.
UNPIVOT nereprodukuje původní výsledek výrazu s hodnotou tabulky, protože řádky byly sloučeny.
NULL Také hodnoty ve vstupu UNPIVOT zmizí ve výstupu. Když hodnoty zmizí, ukazuje, že před operací mohlo dojít k původním NULL hodnotám PIVOT ve vstupu.
Zobrazení Sales.vSalesPersonSalesByFiscalYears v AdventureWorks2025 ukázkové databázi používá PIVOT k vrácení celkového prodeje pro každého prodejce za každý fiskální rok. Pokud chcete skriptovat zobrazení v aplikaci SQL Server Management Studio, v Průzkumníku objektů vyhledejte zobrazení ve složce Zobrazení pro AdventureWorks2025 databázi. Klikněte pravým tlačítkem myši na název zobrazení a pak vyberte Zobrazení skriptu jako.