Szybki kontakt




Niestandardowe operatory PIVOT i UNPIVOT.

MS SQL Server umożliwia prezentację danych w formie tabel przestawnych. Za pomocą niestandardowych operatorów PIVOT i UNPIVOT możesz przekształcić kolumny w wiersze i odwrotnie.

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

SQL zapytanie wyjściowe do pivot

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.

SSMS okno aktywne sesje 1

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

SSMS okno aktywne sesje 2

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ę zawierającą argumenty funkcji grupującej – 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

SQL operator pivot 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ć:

Use AdventureWorks2012;
SELECT * 
FROM #tab_pivot

Wynik zapytania

SQL dane tabela przestawna

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

SQL unpivot wynik zapytania

Dodaj komentarz

Twój adres email nie zostanie opublikowany.