OD – Korzystanie z funkcji PIVOT i UNPIVOT

• 14.10.2019
• 5 minut na przeczytanie
• • V
  • c
  • M
  • i
  • M
  • +7

Dotyczy: SQL Server (wszystkie obsługiwane wersje) Azure SQL Database Wystąpienie zarządzane Azure SQL Azure Synapse Analytics Równoległa hurtownia danych

Możesz użyć operatorów relacyjnych PIVOT i UNPIVOT, aby zmienić wyrażenie wyceniane w tabeli na inną tabelę. PIVOT obraca wyrażenie wycenione w tabeli, zamieniając unikalne wartości z jednej kolumny wyrażenia na wiele kolumn w wyniku. I PIVOT uruchamia agregacje, w których są one „wymagane na wszelkich pozostałych wartościach kolumn, które są wymagane w końcowym wyniku. UNPIVOT wykonuje odwrotną operację do PIVOT poprzez obrócenie kolumn wyrażenia wycenionego w tabeli na wartości kolumn.

Składnia PIVOT zapewnia prostszą i bardziej czytelną składnię niż składnia, która mogłaby być inaczej określony w złożonej serii instrukcji SELECT...CASE. Pełny opis składni PIVOT można znaleźć w sekcji FROM (Transact-SQL).

Składnia

Poniższa składnia podsumowuje sposób korzystania z operatora PIVOT.

Uwagi

Identyfikatory kolumn w klauzuli UNPIVOT są zgodne z porządkiem katalogu. W przypadku SQL Database sortowanie jest zawsze SQL_Latin1_General_CP1_CI_AS. W przypadku programu SQL Server częściowo zawiera baz danych, sortowanie jest zawsze Latin1_General_100_CI_AS_KS_WS_SC. Jeśli kolumna jest kombinacją ed z innymi kolumnami, wymagana jest klauzula sortowania (COLLATE DATABASE_DEFAULT), aby uniknąć konfliktów.

Podstawowy przykład PIVOT

Poniższy przykład kodu tworzy tabelę z dwiema kolumnami i czterema wierszami.

Oto zestaw wyników.

Żadne produkty nie są zdefiniowane z trzema DaysToManufacture.

Poniższy kod wyświetla ten sam wynik po obróceniu tak, że DaysToManufacture wartości stają się nagłówkami kolumn. Podana jest kolumna dla trzech dni, mimo że wyniki są NULL.

Oto zestaw wyników .

Złożony przykład PIVOT

Typowy scenariusz, w którym PIVOT może być przydatny, to gdy chcesz wygenerować raporty zestawień krzyżowych, aby przedstawić podsumowanie danych. Na przykład załóżmy, że chcesz wykonać zapytanie w tabeli PurchaseOrderHeader w przykładowej bazie danych AdventureWorks2014, aby określić liczbę zamówień złożonych przez określonych pracowników. Poniższe zapytanie dostarcza ten raport uporządkowany według dostawcy.

Oto częściowy zestaw wyników.

Wyniki zwrócone przez tę podselekcję instrukcja jest przestawiana w kolumnie EmployeeID.

Unikalne wartości zwracane przez stanie się polami w końcowym zestawie wyników. W związku z tym istnieje „jedna kolumna dla każdego EmployeeID numeru określonego w klauzuli pivot: w tym przypadku pracownicy 250, 251, 256, 257 i 260. PurchaseOrderID służy jako kolumna wartości, względem której grupowane są kolumny zwracane w końcowym wyniku, zwane kolumnami grupującymi. W tym przypadku kolumny grupujące są agregowane przez COUNT. Zauważ, że pojawia się komunikat ostrzegawczy, który wskazuje, że wszelkie wartości null pojawiające się w kolumnie PurchaseOrderID nie zostały uwzględnione podczas obliczania COUNT dla każdego pracownika.

Przykład UNPIVOT

UNPIVOT wykonuje prawie odwrotną operację PIVOT, 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 Emp1, Emp2, Emp3, Emp4 i Emp5 do wartości wierszy odpowiadających określonemu dostawcy. W związku z tym musisz zidentyfikować dwie dodatkowe kolumny.Kolumna, która będzie zawierać wartości kolumn, które „obracasz ponownie (Emp1, Emp2, …) będzie miała nazwę Employee, a kolumna, która będzie zawierała wartości, które aktualnie istnieją pod obracanymi kolumnami, będzie nosiła nazwę Orders. Te kolumny odpowiadają przestawnej_kolumnie i wartości_kolumnie odpowiednio w definicji języka Transact-SQL. Oto zapytanie.

Oto częściowy zestaw wyników.

Zwróć uwagę, że UNPIVOT nie jest „dokładnie odwrotnością PIVOT. PIVOT przeprowadza agregację i scala możliwe wiele wierszy w jeden wiersz w wyniku. UNPIVOT nie odtwarza oryginalnego wyniku wyrażenia wycenionego w tabeli, ponieważ wiersze zostały scalone. Znikają również puste wartości na wejściu UNPIVOT w danych wyjściowych. Kiedy wartości znikają, oznacza to, że przed operacją PIVOT mogły znajdować się oryginalne wartości null.

Sales.vSalesPersonSalesByFiscalYears w przykładowej bazie danych AdventureWorks2012 używa PIVOT do zwracania łącznej sprzedaży dla każdego sprzedawcy w każdym roku obrachunkowym. Aby utworzyć skrypt widoku w programie SQL Server Management Studio , w Eksploratorze obiektów zlokalizuj widok w folderze Widoki dla bazy danych AdventureWorks2012. Kliknij prawym przyciskiem myszy nazwę widoku, a następnie wybierz Widok skryptu jako.

Zobacz także

Z (Transact -SQL)
CASE (Transact-SQL)