Data: 8.07.2021

MS SQL operatory pivot i unpivot

Spis treści

• MS SQL operatory pivot i unpivot
  • MS SQL operatory pivot i unpivot – do czego służą?
  • Operator PIVOT
  • Operator UNPIVOT

Za pomocą niestandardowych operatorów PIVOT i UNPIVOT w języku MS SQL możesz przestawić dane z kolumn w wiersze i odwrotnie.

MS SQL operatory pivot i unpivot – do czego służą?

Operatory PIVOT i UNPIVOT w SQL są najczęściej wykorzystywane w raportowaniu i przygotowywaniu danych do wizualizacji.

Operator PIVOT umożliwia przekształcenie wierszy w kolumny. Jest przydatny, gdy chcemy zgrupować dane i przedstawić je w układzie tabelarycznym np. możemy przedstawić listę miesięcznych przychodów w kolumnach odpowiadającym poszczególnym miesiącom.

UNPIVOT działa odwrotnie – przekształca kolumny w wiersze. Przykładowo, jeśli dla każdego miesiąca mamy oddzielną kolumnę, za pomocą operatora UNPIVOT możemy wszystkie miesiące umieścić w wierszach w jednej kolumnie i nadać jej nazwę Miesiąc.

Operator PIVOT

Operator PIVOT:

• przekształca dane z wierszy w kolumny
• grupuje dane i wywołuje dla każdej grupy wskazaną funkcję agregującą (grupującą).

W wyniku przekształcenia otrzymujemy zbiór danych, w których informacje zaprezentowane są na wzór tabel przestawnych z programu MS Excel.

Zapytanie do bazy danych. Odczytujemy informacje, na podstawie których zbudujemy operatorem PIVOT tabelę przestawną.

Use AdventureWorks2012;
SELECT a.ProductID, YEAR(b.OrderDate) AS Rok, SUM(a.LineTotal) AS Sprzedaż
     FROM Sales.SalesOrderDetail a
     JOIN Sales.SalesOrderHeader b
     ON a.SalesOrderID = b. SalesOrderID
GROUP BY a.ProductID, YEAR(b.OrderDate)
ORDER BY a.ProductID, YEAR(b.OrderDate);

Wynik zapytania:

W wyniku zapytania grupy (ProductID) i podgrupy (Rok) oraz wyliczone dla nich sumy (Sprzedaż) wyświetlone są w kolejnych wierszach.

Za pomocą operatora PIVOT możemy przedstawić dane w formie tabeli przestawnej. Produkty (ProductID) przedstawimy w wierszach a lata (Rok) w kolumnach. Na przecięciu wiersza i kolumny umieścimy sumę sprzedaży danego produktu w poszczególnych latach. Prezentacja danych w ten sposób ułatwi ich odczytanie i analizę.

Odczytane dane zapisujemy do tymczasowej tabeli o nazwie #tab_przestawna

Use AdventureWorks2012;
SELECT a.ProductID, YEAR(b.OrderDate) AS Rok, SUM(a.LineTotal) AS Sprzedaż
INTO #tab_przestawna
     FROM Sales.SalesOrderDetail a
     JOIN Sales.SalesOrderHeader b
     ON a.SalesOrderID = b. SalesOrderID
GROUP BY a.ProductID, YEAR(b.OrderDate)
ORDER BY a.ProductID, YEAR(b.OrderDate);

Uwaga! Tabele tymczasowe poprzedzone jednym znakiem # lub dwoma znakami ## nie są fizycznie zapisywane w bazie danych. Dostępne są one tylko w trakcie trwania sesji SSMS (SQL Server Management Studio).

• #nazwa_tabeli – tabele lokalne – widoczne tylko w ramach jednej aktywnej sesji
• ##nazwa_tabeli – tabele globalne – widoczne w ramach wszystkich aktywnych sesji.

Tabela tymczasowa o nazwie #tab_przestawna widoczna będzie tylko w sesji numer 1 (w tej, w której została utworzona).

Tabela tymczasowa o nazwie ##tab_przestawna widoczna będzie w sesji numer 1, 2.

Jeżeli do utworzenia raportu tabeli przestawnej użyjemy tabeli tymczasowej #tab_przestawna, wszystkie zapytania musimy uruchomić po kolei, w ramach tej samej sesji, w której utworzona została tabela tymczasowa #tab_przestawna.

Przekształcanie wierszy na kolumny przebiega w następujących etapach:

• pogrupowanie danych według wartości tej kolumny, która będzie zawierała nagłówki wierszy. W przykładzie jest to kolumna ProductID. Następuje tutaj niejawne grupowanie, gdyż nazwa kolumny ProductID nie pojawia się na liście parametrów operatora PIVOT.
• przekształcenie nagłówków kolumn – do utworzonych kolejnych kolumn kopiowane są dane ze wskazanej kolumny tabeli. W przykładzie dane z kolumny LineTotal.
• wywołanie dla każdego pola utworzonej tabeli funkcji grupującej. W przykładzie jest to funkcja SUM(), za pomocą której sumujemy i grupujemy dane z kolumny LineTotal.

Use AdventureWorks2012;
SELECT P.ProductID, [2005], [2006],[2007], [2008]
FROM #tab_przestawna
PIVOT (
SUM(Sprzedaż)
FOR Rok IN ([2005], [2006],[2007], [2008]) ) AS P
ORDER BY P.ProductID;

W powyższym zapytaniu:

• w klauzuli SELECT definiujemy listę kolumn tabeli przestawnej: pierwsza kolumna zawiera identyfikatory produktów, kolejne kolumny zawierają sumę sprzedaży w poszczególnych latach. Lista kolumn [2005], [2006],[2007], [2008] wpisana została ręcznie.
• za pomocą operatora PIVOT określamy:
  • funkcję grupującą – w naszym przykładzie jest to funkcja SUM()
  • kolumnę, z której dane będą sumowane – w naszym przykładzie jest to kolumna Sprzedaż
  • kolumnę bazową, z której odczytane zostaną dane do umieszczenia w kolumnach [2005], [2006],[2007], [2008]. W naszym przykładzie jest to Rok
• kolumny ProductID używamy do pogrupowania danych. Kolejne wiersze wyniku zapytania zawierają informacje o sprzedaży poszczególnych produktów.

Wynik zapytania – tabela przestawna

Tablę tymczasową, użytą do tworzenia tabeli przestawnej, można zamienić na podzapytanie.

Use AdventureWorks2012;
SELECT P.ProductID, [2005], [2006],[2007], [2008]
FROM
    (SELECT a.ProductID, YEAR(b.OrderDate) AS Rok,
            SUM(a.LineTotal) AS Sprzedaż
        FROM Sales.SalesOrderDetail a
    JOIN Sales.SalesOrderHeader b
        ON a.SalesOrderID = b. SalesOrderID
     GROUP BY a.ProductID, YEAR(b.orderdate)
      ) a
PIVOT (
SUM(Sprzedaż)
FOR Rok IN ([2005], [2006],[2007], [2008]) ) AS P
ORDER BY P.ProductID;

Informacje na temat podzapytań znajdziesz w artykule pod linkiem MS SQL – podzapytania.

Operator UNPIVOT

Działanie operatora UNPIVOT polega na odwróceniu wyniku działania operatora PIVOT. Za jego pomocą zamieniamy kolumny na wiersze i rozbijamy niejawnie utworzone grupy w tabeli przestawnej.

Tworzymy tabelę przestawną i wynik zapisujemy do tabeli tymczasowej o nazwie #tab_pivot

Use AdventureWorks2012;
SELECT a.ProductID, [2005], [2006],[2007], [2008]
INTO #tab_pivot
FROM #tab_przestawna
  PIVOT (
  SUM(Sprzedaż)
  FOR Rok IN ([2005], [2006],[2007], [2008]) ) AS a
  ORDER BY a.ProductID;

Dane z tabeli tymczasowej #tab_pivot możemy odczytać za pomocą poniższego polecenia:

Use AdventureWorks2012;
SELECT *
FROM #tab_pivot

Przekształcenie kolumn na wiersze, za pomocą operatora UNPIVOT, przebiega w następujących etapach:

• wygenerowanie duplikatów wartości kolumny wskazanej w bloku IN
• utworzenie kolumny o nazwie wskazanej w bloku FOR
• usunięcie z wyniku wartości specjalnej NULL. Usunięcie wartości NULL powoduje, że operatory PIVOT i UNPIVOT są niesymetryczne. Po przekształceniu wierszy na kolumny a następnie kolumn na wiersze wynik zapytania może zawierać mniej danych.

SELECT a.ProductID, a.Rok, a.Sprzedaż
FROM #tab_pivot
UNPIVOT (Sprzedaż FOR Rok IN ([2005], [2006], [2007], [2008])) AS a;

W powyższym zapytaniu:

• w klauzuli SELECT definiujemy listę kolumn wyniku
• w klauzuli FROM wskazujemy tabelę źródłową
• za pomocą operatora UNPIVOT definiujemy kolumnę, w której zostaną umieszczone odczytane z tabeli przestawnej podsumowania (kolumna Sprzedaż), oraz określamy kolumnę, w której zostaną umieszczone nagłówki kolumn tabeli przestawnej (kolumna Rok)

Wynik zapytania:

Zachęcamy do udziału w naszym kursie SQL poziom średnio-zaawansowany, na którym przećwiczysz w praktyce m.in. operatory PIVOT i UNPIVOT.

Jarek Olechno
Programista SQL, Cloud Developer

Udostępnij wpis: