Tabela przestawna w Excelu — jak zrobić krok po kroku

Pivotowanie tabeli w SQL przenosi wartości z wierszy do kolumn, tworząc układ tabeli przestawnej dla zagregowanych danych. Można to zrobić operatorem PIVOT albo agregacją warunkową. Warto też wiedzieć, skąd biorą się puste komórki i dodatkowe wiersze wyniku.
Operator PIVOT wprowadzono w SQL Server 2005¹.

Czym jest pivotowanie tabeli w SQL i jak działa operator PIVOT?

W SQL operator PIVOT oblicza wskazaną miarę osobno dla każdej wartości, która ma stać się nagłówkiem kolumny. Jeśli nazwy kolumn zależą od zawartości danych, dynamiczny pivot tworzy ich listę podczas przygotowywania zapytania.
Pojedyncza klauzula PIVOT dopuszcza jedną funkcję agregującą przed częścią FOR… IN².

Jak operator PIVOT zamienia wiersze na kolumny?

PIVOT tworzy nagłówki z wartości kolumny źródłowej, a zawartość nowych kolumn wylicza funkcją agregującą. Przy łączeniu kilku miar z miesiącami CROSS APPLY zamienia wartości czterech kolumn na wiersze. Nadaje im etykiety złożone ze skrótu miesiąca i nazwy kolumny³. Wyrażenie SUBSTRING([Month],1,3) ogranicza skrót miesiąca do 3 znaków³.
Etykiety te mogą następnie stać się kolumnami wyniku. Przy czterech miarach CROSS APPLY z czterema pozycjami VALUES tworzy 4 wiersze pośrednie z każdego wiersza źródłowego⁴.

W innym przykładzie dynamiczna lista kolumn powstaje z odrębnych wartości coluName. Funkcja QUOTENAME ujmuje nazwy w identyfikatory SQL, a sp_executesql wykonuje zbudowane zapytanie⁵.
O układzie kolumn decydują więc wartości obecne w danych. QUOTENAME przyjmuje nazwę o długości najwyżej 128 znaków; dla dłuższego argumentu zwraca NULL⁶.

Kiedy użyć operatora PIVOT, a kiedy agregacji warunkowej?

PIVOT wybierz wtedy, gdy wartości jednej kategorii mają stać się kolumnami wyniku. Agregacja warunkowa z CASE daje z kolei możliwość zapisania osobnego warunku dla każdej kolumny. W obu metodach trzeba wskazać miarę i sposób agregacji.
Wybór zależy od oczekiwanego układu oraz od tego, czy kolumny mają wynikać z danych, czy z warunków zapytania. Jeden operator PIVOT definiuje jedną agregację; osobne wyrażenia CASE pozwalają przypisać kolumnom różne warunki⁷.

Tabela porównuje obie metody według sposobu definiowania kolumn i warunku ich użycia.

Funkcję agregującą dobiera się do miary. W przykładzie z coluName operator PIVOT oblicza MAX(amount) dla poszczególnych wartości tej kolumny⁵. W przykładzie z Sales.Orders dynamiczny pivot tworzy kolumny z wartości custid. Funkcja COUNT(orderid) zlicza w nich zamówienia według empid⁸.
PIVOT ustala układ kolumn, a funkcja agregująca wyznacza wartości w komórkach. Gdy brakuje danej kombinacji kategorii, PIVOT z COUNT zwraca 0, a PIVOT z SUM – NULL².

Jak zbudować tabelę przestawną w SQL za pomocą CASE i funkcji agregujących?

Tabelę przestawną w SQL zbudujesz też bez operatora PIVOT, łącząc CASE z funkcją agregującą. Warunek kieruje wartość do odpowiedniej kolumny, a funkcja oblicza wynik dla wybranej grupy. Pola przed agregacją można przygotować w CTE, czyli nazwanym wyniku pomocniczego zapytania.
SQL Server dopuszcza maksymalnie 10 poziomów zagnieżdżenia wyrażeń CASE⁹.

• Przygotowanie danych obejmuje wyliczenie pól potrzebnych do grupowania. W tabeli Rhema_GoldExportSummary nazwa miesiąca i rok są wyliczane z kolumny DateCreated³.
• Wybór układu określa pole, które pozostanie w wierszach wyniku, oraz wartości przeznaczone na osobne kolumny.
• Warunek CASE przypisuje mierzoną wartość do właściwej kolumny, jeśli wiersz spełnia wskazany warunek.
• Funkcja agregująca, na przykład SUM lub COUNT, oblicza wynik wyrażenia CASE. Klauzula GROUP BY ustala poziom grupowania wierszy.

Wynik zapytania pokazuje wybrane kategorie w kolumnach, a zagregowane wartości w wierszach wyznaczonych przez GROUP BY. Dla trzech kategorii, na przykład lat 2019–2021, trzy wyrażenia SUM(CASE…) tworzą trzy kolumny zagregowanych wartości⁷.

Dlaczego po pivotowaniu brakuje wartości lub przybywa wierszy?

Puste komórki pojawiają się, gdy dla danej kombinacji kategorii nie ma wartości do zagregowania. Dodatkowe wiersze powstają z kolei wtedy, gdy zapytanie uwzględnia niezamierzone pola grupowania albo dodaje sumy. GROUPING SETS pozwala wskazać osobne zestawy pól, więc wiersz sumaryczny jest wynikiem agregacji, nie dodatkowym rekordem źródłowym.
W przykładzie funkcja oblicza wartości według roku, klienta i etykiety oraz wartości łączne według etykiety³. W GROUPING SETS ((empid), ()) zestaw (empid) tworzy wiersze dla pracowników, a pusty zestaw () – wiersz sumy całkowitej¹⁰.

• Puste komórki warto sprawdzić, porównując kategorie wybrane na kolumny z kombinacjami obecnymi w danych źródłowych. Pivot nie obliczy wartości dla kombinacji, której w tych danych nie ma.
• Dodatkowe wiersze wymagają sprawdzenia pól grupowania i zestawów agregacji. Każdy odmienny zestaw pól zmienia poziom szczegółowości wyniku.
• Wiersz sumaryczny można przesunąć na koniec wyniku warunkiem ORDER BY CASE WHEN ClientID IS NULL THEN 1 ELSE 0 END ASC³. Warunek nadaje wierszom z ClientID równym NULL klucz sortowania 1, a pozostałym – 0.

Kontrola danych źródłowych i poziomu grupowania pomaga odróżnić pustą komórkę od celowo dodanego wiersza sumarycznego.
Funkcja GROUPING() zwraca 1 dla kolumny zagregowanej w wierszu sumarycznym, a 0 dla kolumny obecnej w grupowaniu¹¹.

Najczęściej zadawane pytania

Od której wersji SQL Server dostępny jest operator PIVOT?

Operator PIVOT wprowadzono w SQL Server 2005.

Jak bezpiecznie ująć dynamiczne nazwy kolumn w zapytaniu SQL?

Funkcja QUOTENAME ujmuje nazwy jako identyfikatory SQL. Przyjmuje nazwy o długości do 128 znaków; dla dłuższej zwraca NULL.

Do czego można wykorzystać CTE przy przygotowaniu pivotu?

CTE, czyli nazwany wynik pomocniczy zapytania, może przygotować pola potrzebne do grupowania przed agregacją.

Ile poziomów zagnieżdżenia wyrażeń CASE dopuszcza SQL Server?

SQL Server dopuszcza maksymalnie 10 poziomów zagnieżdżenia wyrażeń CASE.