Dynamiczne tabele przestawne w SQL (Pivot Table, Dynamic SQL)

Uwaga! ten artykuł mówi o bardziej zaawansowanych, dynamicznych tabelach przestawnych. Jeżeli interesują Cię raczej podstawowe informacje o tym jak zrobić tabelę przestawną w SQL, to zajrzyj raczej do tego artykułu, a potem możesz zajrzeć znowu tutaj.

Język SQL posiada możliwość budowania tabel przestawnych. Zazwyczaj użytkownicy korzystający z tabel przestawnych preferują korzystanie ze specjalistycznych narzędzi analitycznych, w tym z Excela, gdzie budowanie tabel przestawnych jest bardzo proste. Jeżeli jednak jest taka potrzeba, to tabele przestawne (PIVOT TABLE) można także budować bezpośrednio w języku SQL.
Niestety budowa polecenia do utworzenia tabeli przestawnej w SQL jest nieco skomplikowana i niestety sztywna. No chyba, że zstosuje się drobną sztuczkę…
Załóżmy, że z tabeli, w której zapisane są kolor (color), rozmiar (size) oraz cena (ListPrice) chcemy zrobić tabelę przestawną, gdzie w wierszach zobaczymy rozmiary, a w kolumnach kolory. Na przecięciu wierszy i kolumn chcemy zobaczyć średnią cenę.

SELECT *
FROM
(
 SELECT Size, Color, ListPrice
 FROM Production.Product
 WHERE Color IS NOT NULL AND Size IS NOT NULL
) AS myProducts
PIVOT (AVG(ListPrice) FOR Color IN ([Black],[Silver])) pvt

Wprawdzie zapytanie wyświetliło tabelę przestawną, ale jej kolumny (czyli kolory) musiałeś samodzielnie wymienić. A w Excelu jest tak pięknie… przeciągasz myszką i kolumny wypełniają się same. No cóż – tutaj też się da, chociaż będzie troszkę trudniej.
Problematycznym miejscem jest to, w którym tworzysz listę kolumn do wyświetlenia:

FOR Color IN ([Black],[Silver])

Żeby tabela przestawna zaprezentowała kolumny dla wszystkich danych musisz wymienić tu wszystki dostępne kolory dla tabeli przestawnej. Ale, ale, czy przypadkiem listy takich kolumn nie można wygenerować automatycznie? Przecież polecenie

SELECT DISTINCT Color
FROM Production.Product
WHERE Color IS NOT NULL

Pokazuje listę dostępnych kolorów!
Gdyby tak tylko:

1. tę listę jakoś zapisać do zmiennej,
2. a potem tą zmienną wstawić do polecenia tworzącego tabelę przestawną,
3. a  potem uruchomić to polecenie…

O rety – no przecież się da. Po kolei. Najpierw definiujemy zmienną i zapisujemy w niej listę kolorów i to na dodatek już z wymaganymi nawiasami kwadratowymi:

DECLARE @PivotColumnHeaders VARCHAR(MAX);
SET @PivotColumnHeaders='';

SELECT 
 @PivotColumnHeaders = @PivotColumnHeaders + '[' + Color + '],'
FROM
(SELECT DISTINCT Color
 FROM Production.Product
 WHERE Color IS NOT NULL) tabColors

SET @PivotColumnHeaders = LEFT(@PivotColumnHeaders,LEN(@PivotColumnHeaders)-1)

Podzapytanie, które tutaj widzisz wybiera unikalne wartości kolorów. Zapytanie zewnętrzne buduje napis z tych nazw kolorów, umieszczając je w nawiasach kwadratowych. Po zakończeniu zapytania zmienna z zapisanymi kolorami kończy się przecinkiem, a nasza lista nie. Dlatego jedna z ostatnich instrukcji wycina ostatni znak ze zmiennej pamiętającej nazwy kolorów.
W ten sposób pierwszy punkt z listy życzeń mamy gotowy.

Jak użyć zmiennej z kolorami w wyrażeniu budowania tabeli przestawnej? Przypomnijmy sobie o dynamicznym SQL. Ma on swoje wady, ale w takim przypadku się przyda. Zrobimy jeden duży napis, który będzie częściowo zbudowany z komendy tworzenia tabeli przestawnej, a częściowo z utworzonej przez nas listy kolumn:

DECLARE @cmd VARCHAR(MAX);
SET @cmd='
SELECT
 *
FROM
(
 SELECT Size, Color, ListPrice
 FROM Production.Product
) AS myProducts
PIVOT (AVG(ListPrice) FOR Color IN ('+@PivotColumnHeaders+')) pvt '

W ten sposób drugi punkt z listy życzeń mamy gotowy.

Ostatnia instrukcja to wykonanie polecenie i zachwyt nad utworzoną tabelą przestawną:

EXECUTE (@cmd)

Przykład do ściągnięcia z mobilo (mobilo24):

http://www.mobilo24.eu/wp-content/uploads/2011/04/dynamiczna_tabela_przestawna.zip

Źródła:

• http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx
• http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx