Using PIVOT and UNPIVOT

Przykład PIVOT podstawowe

W poniższym przykładzie kodu daje tabela dwie kolumna, która ma cztery wiersze.

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

Here is the result set.

DaysToManufacture          AverageCost

0                          5.0885

1                          223.88

2                          359.1082

4                          949.4105

Produkty nie są zdefiniowane z trzema DaysToManufacture.

Następujący kod wyświetla ten sam wynik, obracane, aby DaysToManufacture wartości stają się nagłówki kolumn. Kolumna jest przewidziana trzy [3] dni, nawet jeżeli wyniki są 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;

Here is the result set.

Cost_Sorted_By_Production_Days    0         1         2           3       4       

AverageCost                       5.0885    223.88    359.1082    NULL    949.4105

Przykład PIVOT złożonych

Wspólnego scenariusza, w którym PIVOT może być przydatna jest w przypadku generowania raportów krzyżowej do podsumowania danych. Załóżmy na przykład, która ma być zbadana PurchaseOrderHeader Tabela w AdventureWorks Przykładowa baza danych w celu określenia liczby zamówień zakupu, wprowadzane przez określonych pracowników. Następująca kwerenda zawiera ten raport, uporządkowane według dostawcy.

USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY pvt.VendorID;

W tym polu jest zestaw wyników częściowych.

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
1           4           3           5           4           4
2           4           1           5           5           5
3           4           3           5           4           4
4           4           2           5           5           4
5           5           1           5           5           5

Zwrócone przez tę instrukcję subselect wyniki są obracane na EmployeeID Kolumna.

SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;

Oznacza to, że unikatowe wartości zwracane przez EmployeeID pola kolumn stają się same w wyniku końcowego zestaw. Dlatego istnieje dla każdej kolumna EmployeeID numer określony w klauzula przestawnych: w tym przypadek pracowników 164, 198, 223, 231, a 233. The PurchaseOrderID kolumna serves as the value kolumna, against which the columns returned in the final output, which are called the grupowanie columns, are grouped. W takim przypadek kolumn grupowanie są agregowane przez COUNT Funkcja. Powiadomienie, że pojawi się komunikat z ostrzeżeniem, wskazuje dowolnego typu null znajdujących się w wartości PurchaseOrderID kolumna nie zostały uznane za podczas przetwarzania danych COUNT dla każdego pracownika.

Important Note:
Funkcje agregujące są używane z PIVOT, występowanie wartości null kolumna wartości nie są uwzględniane podczas przetwarzania danych agregacja.

UNPIVOT wykonuje prawie odwrotna operacja PIVOT, obracanie kolumn na wiersze.Załóżmy, że tabela produkowane w poprzednim przykładzie jest przechowywana w bazie danych jako pvt, a chcesz obrócić identyfikatory kolumna Emp1, Emp2, Emp3, Emp4, a Emp5 w wierszu wartości, które odnoszą się do wybranego dostawcy. Oznacza to, że musisz określić dwa dodatkowe kolumny.kolumna zawierające wartości kolumn, że są obracanie)Emp1, Emp2...) będzie wywoływana Employee, a kolumna, które będą przechowywane wartości, które aktualnie znajdują się w kolumnach jest obrócony będzie wywoływana Orders. Kolumny te odpowiadają pivot_column i value_column, odpowiednio w Transact-SQL Definicja. W tym polu jest kwerenda.

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

W tym polu jest 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
...

Należy zauważyć, że UNPIVOT nie jest dokładnym odwrotnej PIVOT.PIVOT wykonuje agregacja i dlatego scala możliwości wielu wierszy pojedynczego wiersza w danych wyjściowych.UNPIVOT nie odtworzyć oryginalny wynik wyrażenie wartości w tabela, ponieważ zostały scalone wiersze.Oprócz wartości null na wejściu UNPIVOT są usuwane w danych wyjściowych, może nastąpił oryginalnej wartości null w danych wejściowych przed operacją PIVOT.

The Sales.vSalesPersonSalesByFiscalYears view in the AdventureWorks sample database uses PIVOT to return the total sales for each salesperson, for each fiscal year.Widok skryptu SQL Server Management Studio, w Eksplorator obiektów, zlokalizuj widoku w obszarze Widoki folder AdventureWorks bazy danych.Kliknij prawym przyciskiem myszy nazwę widoku, a następnie wybierz opcję Widok skryptu jako.

See Also

Other Resources