Używanie operatorów PIVOT i UNPIVOT
Aby zmienić wyrażenie zwracające tabelę w inną tabelę, można używać operatorów relacyjnych PIVOT i UNPIVOT.Operator PIVOT obraca wyrażenie zwracające tabelę przez przekształcenie unikatowych wartości z jednej kolumny wyrażenia w wiele kolumn w danych wyjściowych, po czym wykonuje agregacje, gdzie są one potrzebne, na wszelkich pozostałych wartościach kolumny, które mają się znaleźć w końcowych danych wyjściowych.Operator UNPIVOT wykonuje operację przeciwną do operatora PIVOT, przekształcając kolumny wyrażenia zwracającego tabelę w wartości kolumny.
Ostrzeżenie
Gdy operatory PIVOT i UNPIVOT są używane na bazach danych, które są uaktualnione do wersji SQL Server 2005 lub nowszej, poziom zgodności bazy danych musi być ustawiony na 90 lub wyższy.Aby uzyskać informacje dotyczące ustawiania poziomu zgodności bazy danych, zobacz sp_dbcmptlevel (języka Transact-SQL).
Składnia operatora PIVOT jest prostsza i bardziej czytelna niż składnia, która byłaby określona w złożonej serii instrukcji SELECT...CASE.Aby zapoznać się z pełnym opisem składni operatora PIVOT, zobacz Z języka Transact-SQL).
Poniżej przedstawiono składnię operatora PIVOT z adnotacjami.
SELECT <non-pivoted column>,
[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 will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
Przykład podstawowej składni z operatorem PIVOT
Poniższy przykład kodu tworzy tabelę dwukolumnową, która ma cztery wiersze.
USE AdventureWorks2008R2 ;
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
Nie są zdefiniowane żadne produkty z trzema DaysToManufacture.
Poniższy kod wyświetla ten sam wynik obrócony tak, że wartości DaysToManufacture stają się nagłówkami kolumn.Kolumna obejmuje trzy [3] dni, mimo iż wyniki mają wartość NULL.
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[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.
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Przykład złożonej składni z operatorem PIVOT
Typowy scenariusz, gdzie operator PIVOT może być przydatny, to gdy chcesz wygenerować raporty tabeli krzyżowej do podsumowywania danych.Na przykład, załóżmy, że chcesz wykonać zapytanie na tabeli PurchaseOrderHeader w przykładowej bazie danych AdventureWorks2008R2, aby ustalić liczbę zamówień zakupu przez niektórych pracowników.Następujące zapytanie wygeneruje ten raport, uporządkowany według dostawców.
USE AdventureWorks2008R2;
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 częściowy zestaw wyników.
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ę podrzędnego wyboru są obracane i formowane w kolumnę EmployeeID.
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;
Oznacza to, że unikatowe wartości zwracane przez kolumnę EmployeeID same stają się polami końcowym zestawie wyników.Dlatego też istnieje kolumna dla każdego numeru EmployeeID określonego w klauzuli PIVOT: w tym przypadku dla pracowników 164, 198, 223, 231 i 233.Kolumna PurchaseOrderID służy jako kolumna wartości, według są grupowane kolumny zwracane w końcowych danych wyjściowych, nazywane kolumnami grupowania.W tym przypadku kolumny grupowania są agregowane przez funkcję COUNT.Należy zwrócić uwagę, że pojawia się komunikat ostrzegawczy wskazujący, że jakiekolwiek wartości null pojawiające się w kolumnie PurchaseOrderID nie zostały uwzględnione przy obliczaniu wartości COUNT dla każdego pracownika.
.gif "Ważna informacja") Ważne: |
|---|
Gdy funkcje agregujące są używane z operatorem PIVOT, obecność jakichkolwiek wartości null w kolumnie wartości nie jest uwzględniana przy obliczaniu agregacji. |
Operator UNPIVOT wykonuje prawie odwrotną operację niż operator PIVOT, obracając kolumny w wiersze.Załóżmy, że tabela wygenerowana w poprzednim przykładzie jest przechowywane w bazie danych jako pvt, i chcesz obrócić identyfikatory kolumn Emp1, Emp2, Emp3, Emp4 i Emp5 w wartości wiersza, które odpowiadają określonemu dostawcy.Oznacza to, że należy zidentyfikować dwie dodatkowe kolumny.Kolumna, która będzie zawierać wartości kolumny, które są obracanie (Emp1, Emp2,...), zostanie nazwana Employee, a kolumna, które będzie przechowywać wartości, które obecnie znajdują się w obracanych kolumnach, zostanie nazwana Orders.Kolumny te odpowiadają odpowiednio wartościom pivot_column i value_column w definicji składni języka 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 częściowy zestaw wyników.
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
...
Należy zauważyć, że operator UNPIVOT nie jest dokładną odwrotnością operatora PIVOT.Operator PIVOT wykonuje agregację i, zatem, scala wiele możliwych wierszy w pojedynczy wiersz w danych wyjściowych.Operator UNPIVOT nie odtwarza wyniku oryginalnego wyrażenia zwracającego tabelę, ponieważ zostały scalone wiersze.Poza tym wartości null w danych wejściowych operatora UNPIVOT znikają w danych wyjściowych, podczas gdy mogły istnieć oryginalne wartości null w danych wejściowych przed operacją PIVOT.
Widok Sales.vSalesPersonSalesByFiscalYears w przykładowej bazie danych AdventureWorks2008R2 używa operatora PIVOT do zwrócenia całkowitej wartość sprzedaży dla każdego sprzedawcy, dla każdego roku obrachunkowego.Aby utworzyć skrypt dla tego widoku w programie SQL Server Management Studio, w eksploratorze obiektów zlokalizuj ten widok w folderze Widoki dla bazy danych AdventureWorks2008R2.Kliknij prawym przyciskiem myszy nazwę widoku, a następnie zaznacz opcję Utwórz skrypt dla widoku jako.