Tabela przestawna w Transact SQL. Tabele przestawne SQL

Jeśli tutaj zajrzałeś, to z całą pewnością wiesz już, co to jest tabela przestawna. Jeśli tego nie wiesz, to proponuję nie czytać tego artykułu, tylko najpierw poszukać informacji o budowaniu tabel przestawnych np. w programie Excel. Tutaj pokażę tylko jak zbudować tabele przestawne z wykorzystaniem języka T-SQL.

Naszym celem jest zbudowanie tabeli prezentującej średni koszt wyprodukowania produktu w zależności od tego, jakiego rozmiaru jest ten produkt i jakiej klasy:

W przypadku bazy danych AdventureWorks2008, dane te znajdziesz w tabeli Production.Product.

Najpierw spróbujmy wyciągnąć informację źródłowe z tabeli. Widać przecież co jest potrzebne. Potrzebujesz Size (rozmiar), Class (klasa) oraz ListPrice (cena). Można by więc napisać tak:

SELECT Size, Class, ListPrice

FROM Production.Product

WHERE Size IS NOT NULL AND Class IS NOT NULL

Klauzula WHERE ma za zadanie odfiltrowanie tych wszystkich produktów, które nie mają ustalonego rozmiaru ani klasy. Sprawdź, że to zapytanie działa:

Dane są tu prezentowane w postaci rekordów – wierszy, a  my chcielibyśmy owszem zobaczyć w wierszach różne rozmiary, ale w kolumnach powinny znaleźć się różne klasy (H – high, M – medium, L – low).

I właśnie tutaj z pomocą przychodzi składnia tworzenia tabel przestawnych. Najpierw rzut oka na wycinek tej składni:

(<Zapytanie produkujące dane>) AS <Alias dla tego zapytania>
PIVOT (
<funkcja agregująca>(<kolumna z agregowaną wartością>) FOR
[<kolumna, która zawiera wartości, które staną się nagłówkami>]     IN ( [pierwsza wartość], [druga wartość]…)
) AS <Alias dla tabeli pivot>

No to po kolei:

• Zapytanie produkujące dane już mamy
• W/w zapytanie jest podzapytaniem i musi w tym przypadku posiadać alias, więc wymyśl go sobie. Ja go nazwę SourceData
• PIVOT to słowo kluczowe i po prostu musi być.
• Funkcja agregująca to u nas AVG – średnia. Ta średnia ma być wyznaczana na podstawie jakieś wartości – u nas AVG(ListPrice), czyli wyliczanie średniej ceny. W ten sposób po raz pierwszy skorzystaliśmy z naszego bazowego zapytania. Ale pozostały w nim jeszcze 2 kolumny, z których nie skorzystaliśmy.
• FOR to słowo kluczowe. Zaraz określisz DLA jakich grup wyznaczać AVG(ListPrice)
• Teraz należy określić w której kolumnie znajdują się nagłówki konstruowanej tabeli przestawnej. W naszej tabeli w nagłówkach miały się pojawiać oznaczenia klas, stąd mowa o kolumnie Class
• IN to kolejne słowo kluczowe. Zaraz w nawiasie ustalisz na jakie wartości z kolumny Class należy rozbić nasze zestawienie.
• Ponieważ w nagłówku ma się pojawić symbol klasy, to trzeba przejrzeć jakie wartości można znaleźć w kolumnie class i je tutaj grzecznie wymienić. Wpisując te wartości umieszczaj je w nawiasie kwadratowym:
  ([L], [M],[H])
• Na końcu znowu pojawia się alias, bo niestety, ale twór, który powstanie po wykonaniu skonstruowanej na razie tabeli nie nadaje się do wyświetlenia na ekranie. To co powstało jest „jakby tabelą”, a dokładniej podzapytaniem wymagającym aliasu, który w kolumnach ma [L], [M] i [H], a w wierszach kolejne Size oraz powyliczaną średnią cenę (ListPrice). Na tym obiekcie trzeba dopiero wykonać SELECT.  Na razie niech to będzie nawet SELECT *.

Zobacz, jak teraz prezentuje się nasze zapytanie:

SELECT * FROM

(SELECT Size, Class, ListPrice

  FROM Production.Product

  WHERE Size IS NOT NULL AND Class IS NOT NULL) AS SourceData

PIVOT
 (

  AVG(ListPrice) FOR Class IN ([L],[M],[H])

 ) AS PivotTable

Wybierasz wszystkie rekordy z
Podzapytania, które dostarcza dane
Dane nie są wzięte żywcem, ale poukładane w tabelę przestawną
Gdzie wylicza się średnią cenę dla klasy L M lub H

A oto wynik:

Jeżeli chcesz, aby kolumny były ładniej opisane zmień ich opisy np. tak:

SELECT Size, [L] AS 'Class L', [M] AS 'Class M', [H] AS 'Class H' FROM

(SELECT Size, Class, ListPrice

  FROM Production.Product

  WHERE Size IS NOT NULL AND Class IS NOT NULL) AS SourceData

PIVOT
(

  AVG(ListPrice)

  FOR Class IN ([L],[M],[H])

) AS PivotTable

Wynik przedstawia pierwsza ilustracja tego artykułu.

Wadą tabel przestawnych w SQL serwerze jest to, że musisz sam wymieniać kolumny tej tabeli. Excel robił to nie jako zamiast użytkownika!. Ten artykuł opowiada, jak zbudować tabelę przestawną dynamiczną, czyli taką, która sama wyszuka jakie wartości znajdują się w kolumnie, która zawiera nagłówki tabeli przestawnej.

O tabelach przestawnych i o wielu innych ciekawych rzeczach w SQL Serwerze opowiada kurs 2278. Zapraszam na moje szkolenia z SQL.