W tym artykule znajduje się pełny przegląd używania operatorów PIVOT i UNPIVOT w SQL Server. Operatory PIVOT i UNPIVOT są podobne do operatorów relacyjnych, które umożliwiają przekształcanie wyrażenia z wartościami przechowywanymi w tabeli w inną tabelę . Obaj operatorzy generują wielowymiarowe raporty, które pozwalają szybko łączyć i porównywać duże ilości danych.
Możemy skorzystać z Operator PIVOT kiedy musimy przekształcić wyrażenia z wartościami przechowywanymi w tabeli. Dzieli unikalne wartości z jednej kolumny do wielu kolumn w efekcie końcowym. To także agregaty pozostałe wartości kolumn wymagane w wyniku końcowym. Operator UNPIVOT konwertuje dane z kolumn wyrażenia z wartościami przechowywanymi w tabeli na wartości kolumn, co jest odwrotnością PIVOT.
Wyjaśnijmy to za pomocą prostego diagramu podanego poniżej:
Po lewej stronie tego rysunku widzimy oryginalny zbiór danych , który ma trzy kolumny: Rok, Region, I Obroty . Następnie po prawej stronie widzimy tabelę PIVOT, która jest tworzona poprzez obracanie Region (wiersze) na Północ i Południe (kolumny) . Po przekształceniu wierszy w kolumny możemy wykonać suma wartości kolumny Sales dla każdego przecięcia kolumn i wierszy tabeli PIVOT.
Najpierw utwórzmy tabelę o nazwie prezentacja_przestawna aby zademonstrować operatory PIVOT i UNPIVOT. Poniższa instrukcja tworzy nową tabelę w określonej przez nas bazie danych:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Następnie wstaw trochę danych do tej tabeli, jak poniżej:
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
Dane możemy zweryfikować za pomocą instrukcji SELECT. Otrzymamy poniższe dane wyjściowe:
Operator PIVOT
Ten operator służy do obracania wyrażeń z wartościami przechowywanymi w tabeli. Po raz pierwszy został wprowadzony w wersji SQL Server 2005. Konwertuje dane z wierszy na kolumny. Dzieli unikalne wartości z jednej kolumny na wiele kolumn, a następnie agreguje pozostałe wartości kolumn wymagane w wyniku końcowym.
Aby utworzyć tabelę PIVOT, musimy wykonać następujące kroki:
- Wybierz podstawowy zbiór danych do przestawienia.
- Twórz tymczasowe wyniki za pomocą tabeli pochodnej lub CTE (wspólne wyrażenie tabelowe).
- Skorzystaj z operatora PIVOT.
Składnia
Poniższa składnia ilustruje użycie PIVOT w SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Jeśli złamiemy ten skrypt, zobaczymy, że ma on dwie oddzielne sekcje. Pierwsza sekcja wybiera dane z tabeli głównej, a druga sekcja określa sposób konstrukcji tabeli PIVOT. Druga część zawiera również specjalne słowa kluczowe, takie jak SUM, FOR i IN. Zobaczmy znaczenie tych słów kluczowych w operatorze PIVOT.
10 z 40
SUMA
Ten operator jest do tego przyzwyczajony agregować wartości z określonej kolumny, która ma zostać użyta w tabeli PIVOT. Musimy go użyć z operatorem PIVOT, aby uzyskać zagregowane wyświetlenia kolumn dla sekcji wartości.
DLA słowa kluczowego
To słowo kluczowe jest używane w instrukcji tabeli PIVOT poinstruuj operatora PIVOT w której kolumnie należy zastosować funkcję PIVOT. Zasadniczo wskazuje nazwy kolumn, które zostaną przekształcone z wierszy na kolumny.
W Słowo kluczowe
To słowo kluczowe wyświetla listę wszystkich unikalnych wartości z kolumny PIVOT, które mają być wyświetlane jako kolumny tabeli PIVOT.
git dodaj wszystko
Przykład
Spróbujmy to zrozumieć na różnych przykładach.
1. Poniższa instrukcja wybiera najpierw kolumnę Rok, Północ i Południe jako dane podstawowe do przestawiania. Następnie utwórz tymczasowy wynik, korzystając z tabeli pochodnej, i na koniec zastosuj operator PIVOT, aby wygenerować ostateczny wynik. Dane wyjściowe są również uporządkowane w rosnącym roku.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
Wykonanie tej instrukcji spowoduje wygenerowanie poniższych danych wyjściowych. Tutaj możemy zobaczyć obliczona suma sprzedaży regionu Północnego i Południowego odpowiadająca wartościom rocznym .
2. To kolejny przykład, w którym obliczymy sumę sprzedaży dla każdego roku odpowiadającą wartościom regionu:
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
Wykonanie tej instrukcji spowoduje wygenerować błąd ponieważ nie możemy bezpośrednio określić wartości liczbowej jako nazwy kolumny.
Jednak SQL Server pozwala nam uniknąć tego problemu, używając nawiasów przed każdą wartością całkowitą. Zaktualizowana instrukcja jest pokazana w następującym fragmencie kodu:
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
To zestawienie zostało wykonane pomyślnie i wyświetla obliczoną sumę sprzedaży dla każdego roku odpowiadającą wartościom regionu:
3. Poprzedni przykład uzyskania tabeli PIVOT jest pomocny, gdy znamy wszystkie możliwe wartości kolumn PIVOT. Ale załóżmy, że w nadchodzącym roku liczba kolumn zostanie zwiększona. Biorąc pod uwagę poprzedni przykład, mamy lata 2010, 2011 i 2012 jako kolumny PIVOT. Nie ma jednak gwarancji, że kolumny te nie ulegną zmianie w przyszłości. Co się stanie, jeśli będziemy mieli dane z 2013 lub 2014 roku, a może i więcej? W takich przypadkach będziemy musieli skorzystać dynamiczna tabela PIVOT zapytania, aby rozwiązać ten problem.
Dynamiczne zapytanie do tabeli PIVOT hermetyzuje cały skrypt PIVOT w procedurze składowanej. Ta procedura zapewni regulowane opcje, co pozwoli nam modyfikować nasze wymagania poprzez zmianę kilku sparametryzowanych wartości.
porównywalna z Javą
Poniższy kod SQL wyjaśnia działanie dynamicznej tabeli PIVOT. W tym skrypcie najpierw pobraliśmy wszystkie odrębne wartości z kolumny PIVOT, a następnie napisaliśmy instrukcję SQL do wykonania z zapytaniem PIVOT w czasie wykonywania. Zobaczmy wynik po wykonaniu tego skryptu:
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
W tym skrypcie utworzyliśmy dwie sparametryzowane zmienne. Jego opis znajduje się poniżej:
@PivotColumn : Ta zmienna przyjmie nazwę kolumny z oryginalnej tabeli, na której utworzono tabelę PIVOT. Na przykład w tym przypadku kolumna „Region” wyświetla wszystkie regiony dostępne w kolumnach.
@PivotList : Ta zmienna przyjmie listę kolumn, które chcemy wyświetlić jako kolumnę wyjściową w tabeli PIVOT.
Wykonanie dynamicznej procedury składowanej
Po pomyślnym utworzeniu dynamicznej procedury składowanej jesteśmy gotowi do jej wykonania. Poniższa instrukcja służy do wywołania dynamicznej procedury składowanej w celu wyświetlenia tabeli PIVOT w czasie wykonywania:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Tutaj określiliśmy nazwę kolumny „ Region ' jako pierwszy parametr i lista kolumn PIVOT jako drugi parametr. Wykonanie skryptu wyświetli następujące dane wyjściowe:
Teraz możemy w przyszłości dodać więcej kolumn w czasie wykonywania, aby wyświetlić tabelę PIVOT, co nie jest możliwe w przypadku dwóch pierwszych przykładów.
Operator UNPIVOT
Jest to metoda odwrotna do operatora PIVOT w SQL Server. Obsługuje ten operator przeciwne działanie PIVOT poprzez konwersję danych z kolumn na wiersze. Operator UNPIVOT obraca również tabelę PIVOT do zwykłej tabeli. Po raz pierwszy został wprowadzony w wersji SQL Server 2005.
Składnia
Następująca składnia ilustruje UNPIVOT w SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Przykład
Pozwól nam zrozumieć, jak UNPIVOT wykonać operację PIVOT na przykładach. Najpierw utworzymy plik oryginalny stół i stół PIVOT a następnie zastosował operator UNPIVOT na tej tabeli.
Poniższy fragment kodu najpierw deklaruje tymczasową zmienną tabeli @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Następnie wstawimy wartości do tej tabeli, jak poniżej:
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
Teraz możemy wykonać operację UNPIVOT, korzystając z poniższej instrukcji:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Wykonanie fragmentu kodu zwróci następujące dane wyjściowe:
ustawienia przeglądarki internetowej
Poniższy fragment kodu jest kolejnym przykładem wykonania najpierw operacji PIVOT, a następnie operacji UNPIVOT na tej samej tabeli w ramach jednego zapytania:
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Wykonanie fragmentu kodu spowoduje wyświetlenie tego samego wyniku:
UWAGA: Proces UNPIVOT jest operacją odwrotną do procedury PIVOT, ale nie jest to dokładne odwrócenie. Ponieważ wiersze zostały scalone podczas obliczania agregatu metodą PIVOT i w wyniku połączenia wielu wierszy w jeden wiersz, operacja UNPIVOT nie może sprawić, że tabela będzie przypominać oryginał. Jeśli jednak operator PIVOT nie połączy wielu wierszy w jeden, wówczas operator UNPIVOT może uzyskać oryginalną tabelę z wyniku PIVOT.
Wniosek
W tym artykule znajduje się pełny przegląd operatorów PIVOT i UNPIVOT w SQL Server oraz konwersja wyrażenia tabelowego na inne. Nie należy nigdy zapominać, że UNPIVOT jest operacją odwrotną do PIVOT, ale nie jest to dokładna odwrotność wyniku PIVOT.