Dynamiczna liczba kolumn w tabelach przestawnych (pivot query) Oracle 10g i 11g

Tabele przestawne w Oracle 10g i 11g mają jedną wadę – wymagana jest znajomość liczby kolumn i wartości jakie trafią do sekcji kolumn. Oracle musi z góry wiedzieć ile kolumn (i jakiego typu) zwróci. Jest to problem, bo często my nie znamy tych wartości z góry. Da się go jednak ominąć.
Możemy oczywiście próbować zrobić:

 WITH dane AS (SELECT NUM, KAT FROM v)
SELECT *
FROM   dane
PIVOT
   ( COUNT(*)
     FOR KAT
     IN (SELECT kat FROM dane)
)

albo

 WITH dane AS (SELECT NUM, KAT FROM v)
SELECT *
FROM   dane
PIVOT
   ( COUNT(*)
     FOR KAT
     IN (ANY)
)

W obu przypadkach dostaniemy błąd “ORA-00936: missing expression”. Pivot query/tabele przestawne wymuszają na nas wpisanie wprost wartości w nawiasach… z jednym wyjątkiem. Podzapytanie w pivot query jest możliwe o ile użyjemy słowa kluczowego XML:

WITH dane AS (SELECT NUM, KAT FROM tabela)
SELECT *
FROM   dane
PIVOT XML
   ( COUNT(*)
     FOR KAT
     IN (SELECT dane FROM tab)
)

oraz

WITH dane AS (SELECT NUM, KAT FROM tabela)
SELECT *
FROM   dane
PIVOT XML
   ( COUNT(*)
     FOR KAT
     IN (ANY)
)

działają poprawnie. Zapytania te jednak nie zwracają dynamicznej liczby kolumn – zwracają za to dokument XML z dynamicznymi znacznikami. To jakie wartości zostaną zwrócone w dokumencie XML wynika z wartości w kolumnie KAT, jaka jest w tabeli na moment uruchomienia zapytania. Nie musimy tych wartości znać podczas pisania zapytania. Uzyskamy zatem w wyniku:

1 <PivotSet><item><column name = "KAT">a</column><column name = "COUNT(*)">1</column></item><item><column name = "KAT">b</column><column name = "COUNT(*)">1</column></item><item><column name = "KAT">c</column><column name = "COUNT(*)">1</column></item></PivotSet>
2 <PivotSet><item><column name = "KAT">k</column><column name = "COUNT(*)">1</column></item></PivotSet>
3 <PivotSet><item><column name = "KAT">a</column><column name = "COUNT(*)">1</column></item><item><column name = "KAT">j</column><column name = "COUNT(*)">1</column></item></PivotSet>

Pozwala to czasem rozwiązać problem dynamicznej liczby kolumn – po prostu w warstwie wyższej, zamiast czytać kolumny z wyniku, czytamy zawartość XML.

Dla tych, którzy brzydzą się takim rozwiązaniem, mogę zaproponować inne. Też nie idealne, ale zawsze przynajmniej działające – rozwiązujące 90% problemów z brakiem znajomości wartości w sekcji kolumn w trakcie pisania kodu dla bazy.

Skoro schemat postępowania dla PIVOT QUERY jest taki:

SELECT [LISTA KOLUMN W SEKCJI WIERSZY],
       [FUNKCJA](CASE WHEN [KOLUMNA SEKCJI KOLUMN] = [WARTOSC DLA KOLUMNY] THEN [KOLUMNA SEKCJI KOLUMN] ELSE NULL END) as [NAZWA KOLUMNY W SEKCJI KOLUMN],
       ...
FROM tabela
GROUP BY [LISTA KOLUMN W SEKCJI WIERSZY];

to zbudujmy sobie takie zapytanie bez znajomości wartości w sekcji kolumn. Chcę uzyskać:

NUM   a   b   c   j   k
1     1   1   1   0   0
2     0   0   0   0   1
3     1   0   0   1   0

z

NUM KAT
1   a
1   b
1   c
2   k
3   j
3   a

bez konieczności wypisania ‘a’,’b’,’c’,’j’,’k’ wprost w zapytaniu. W końcu zapytanie pisze teraz, a ma działać z danymi jakie ktoś wprowadzi jutro! Da się? Da!

Poniższe zapytanie działa dla wersji 11g

WITH dane AS (SELECT DISTINCT KAT FROM v)
SELECT 'SELECT NUM,' ||
       LISTAGG('COUNT(CASE WHEN KAT = ''' || KAT || ''' THEN KAT ELSE NULL END) AS "'|| KAT ||'"', ',') WITHIN GROUP (ORDER BY KAT) ||
       ' FROM tabela GROUP BY NUM'
FROM dane;

a poniższe w wersjach niższych

WITH dane AS (SELECT DISTINCT KAT, Dense_Rank() OVER (ORDER BY KAT) AS RN FROM v)
SELECT 'SELECT NUM,' ||
       LTrim(sys_connect_by_path('COUNT(CASE WHEN KAT = ''' || KAT || ''' THEN KAT ELSE NULL END) AS "'|| KAT ||'"', ','), ',') ||
       ' FROM v GROUP BY NUM'
FROM dane
WHERE connect_by_isleaf = 1
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1

Oba generują “w locie” treść zapytania, po wykonaniu którego dostaniemy tabele przestawną, ze wszystkimi wartościami z kolumny KAT w sekcji kolumn. Innymi słowy wynikiem powyższych zapytań jest:

SELECT NUM,COUNT(CASE WHEN KAT = 'a' THEN KAT ELSE NULL END) AS "a",COUNT(CASE WHEN KAT = 'b' THEN KAT ELSE NULL END) AS "b",COUNT(CASE WHEN KAT = 'c' THEN KAT ELSE NULL END) AS "c",COUNT(CASE WHEN KAT = 'j' THEN KAT ELSE NULL END) AS "j",COUNT(CASE WHEN KAT = 'k' THEN KAT ELSE NULL END) AS "k" FROM v GROUP BY NUM

czyli to co wcześniej dziergaliśmy na piechotę. Teraz jednak nie musimy znać wartości trafiających do sekcji kolumn z góry! Za to musimy uruchomić dwa zapytania.

Rozwiązanie nie jest co prawda idealne – bo wymaga 2 kroków, ale i tak efekt jest imponujący.

Schemat postępowania w ogólnym przypadku:

Dla wersji 11g

WITH dane AS (SELECT DISTINCT [kolumna z wartościami do sekcji kolumn] FROM tabela)
SELECT 'SELECT [kolumny sekcji wierszy],' ||
       LISTAGG('[funkcja agregująca](CASE WHEN [kolumna z wartościami do sekcji kolumn] = ''' || [kolumna z wartościami do sekcji kolumn] || ''' THEN [kolumna z wartościami do sekcji kolumn] ELSE NULL END) AS "'|| [kolumna z wartościami do sekcji kolumn] ||'"', ',') WITHIN GROUP (ORDER BY [kolumna z wartościami do sekcji kolumn]) ||
       ' FROM tabela GROUP BY [kolumny sekcji wierszy]'
FROM dane;

lub działające zarówno w 11g jak i niższych:

WITH dane AS (SELECT DISTINCT [kolumna z wartościami do sekcji kolumn], Dense_Rank() OVER (ORDER BY [kolumna z wartościami do sekcji kolumn]) AS RN FROM tabela)
SELECT 'SELECT [kolumny sekcji wierszy],' ||
       LTrim(sys_connect_by_path('COUNT(CASE WHEN [kolumny sekcji wierszy] = ''' || [kolumny sekcji wierszy] || ''' THEN [kolumny sekcji wierszy] ELSE NULL END) AS "'|| [kolumny sekcji wierszy] ||'"', ','), ',') ||
       ' FROM tabela GROUP BY [kolumny sekcji wierszy]'
FROM dane
WHERE connect_by_isleaf = 1
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1