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

4-Kwi-2011

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ę.

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:

Ż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

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:

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:

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

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

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:

Komentarze:

  1. Mobilo » Blog Archive » Tabela przestawna w SQL Serverze napisał,

    […] 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

  2. Zibi77 napisał,

    Znakomity artykuł.
    U mnie nie zadział ostatni krok, czyli: EXECUTE (@cmd).
    Musiałem użyć składni: exec sp_executesql @cmd i wszystko śmiga.

  3. Rafał Kraik napisał,

    Tak to prawda. Należy dopisać EXEC. Każde uruchomienie procedury składowanej wymaga poprzedzenia instrukcja EXEC (za wyjątkiem pierwszej w batchu o ile występuje na samym początku tego batcha). Jak widać kiedy pisałem artykuł uruchamiałem instrukcje jedna po drugiej ręcznie i dlatego (standardowa odpowiedź programisty) u mnie działało. Dziękuję za uwagę i już poprawiam 🙂

  4. R napisał,

    A jak próbuje coś identycznie ale w kolumnach mam daty w formacie ‚yyyy-mm-dd’ to już nie wychodzi, krzyczy że
    The data types varchar and date are incompatible in the add operator.
    Co mozna zrobić w takim przyapdku? z góry dzięki. pozdr R

  5. R napisał,

    jakoś sobie poradziłem wprowadzając nowy parametr – data zamieniona na varchar. Teraz mam inny problem kolumny z datami sa ustawione nie kolejno …
    order by ani w selectie z distinct ani w exec nie działa:
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
    da się to jakoś posortować? pozdr R

  6. Rafał Kraik napisał,

    Polecam
    http://davidduffett.net/post/5334646215/get-a-comma-separated-list-of-values-in-sql-with-for

    Przedstawiono tu sposób na zbudowanie uporządkowanej listy wartości rozdzielanej przecinkami:
    SELECT STUFF
    (
    SELECT ‚,’ + ModelThisFits
    FROM ModelProductFits
    ORDER BY ModelThisFits
    FOR XML PATH(‚Model’)
    ), 1, 1, ”

  7. Zibi77 napisał,

    Dla ścisłości pragnę tylko zauważyć, iż składnia:
    SELECT STUFF
    (
    SELECT ‚,’ + ModelThisFits
    FROM ModelProductFits
    ORDER BY ModelThisFits
    FOR XML PATH(‚Model’)
    ), 1, 1, ”

    jest nieprawidłowa gdyż zawartość, którą chcemy stuffować należy obłożyć jeszcze nawiasami i powinno to wyglądać tak:

    SELECT STUFF
    (
    (
    SELECT ‚,’ + ModelThisFits
    FROM ModelProductFits
    ORDER BY ModelThisFits
    FOR XML PATH(‚Model’)
    ), 1, 1, ”
    )

    Ja bym jednak nie szedł w tę stronę, gdyż zawartość nagłówka w pivocie i tak musisz mieć w formacie tekstowym w nawiasach kwadratowych. Sugerowałbym pozostanie przy rozwiązaniu pierwotnie zaproponowanym przez autora tego postu. Do uzyskania sortowania w pivocie można posłużyć się danymi z innej tabelki gdzie już będą posortowane np.

    create table #TwojaTabela (
    id int identity (1,1),
    TwojaData nvarchar (10)
    )

    truncate table #TwojaTabela
    insert into #TwojaTabela
    select cast(cast(ta_data as date) as nvarchar)
    from Jakies_dane
    group by ta_data
    order by ta_data

    DECLARE @PivotColumnHeaders1 nvarchar (max), @cmd1 nvarchar (max)
    SET @PivotColumnHeaders1=”
    SET @cmd1 = ”

    SELECT
    @PivotColumnHeaders1 = @PivotColumnHeaders1 + ‚[‚ + TwojaData + ‚],’
    FROM #TwojaTabela

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

    Zamiast select distinct do uzyskania unikalności daty stosujesz group by i wtedy zadziała order by

Dodaj komentarz:

Autor: Rafał Kraik