Tabele przestawne to temat szeroko znany z Excela lub OpenOffice’a (Pilot danych), który sprowadza się do tego, aby dane widziane w formie ciągu wierszy zaprezentować w formie bardziej zwartej – tak aby zarówno w wierszach, jak i kolumnach znajdowały się interesujące nas wartości wymiarów, a na ich przecięciu wartość interesującej nas miary.
W najprostszym przypadku ich idea sprowadza się do zamiany
NUM KAT 1 a 1 b 1 c 2 k 3 j 3 a
Na:
NUM a b c k j 1 1 1 1 0 0 2 0 0 0 1 1 3 1 0 0 0 1
Tutaj wartościami w sekcji wierszy są NUM, wartościami w sekcji kolumn KAT, a wartością mierzoną COUNT(*).
Spotyka się również pewne odmiany problemu – np. uzyskanie w efekcie:
NUM kol1 kol2 kol3 1 a b c 2 k 3 j a
Ten przypadek omawiam na końcu.
Klasyczne tabele przestawne
Najpierw zajmiemy się przypadkiem częstszym, a więc tym pierwszym. Uzyskanie wyniku w 10g można zrealizować tak:
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 = 'k' THEN KAT ELSE NULL END) as "k",
COUNT(CASE WHEN KAT = 'j' THEN KAT ELSE NULL END) as "j"
FROM tabela
GROUP BY num;
Postępujemy zatem wg. schematu:
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];
W Oracle 11g dodano składnie PIVOT:
SELECT ...
FROM ...
PIVOT
( pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
pivot_clause to funkcja wyliczająca (u nas COUNT(*))
pivot_for_clause to kolumna sekcji kolumn (u nas KAT)
pivot_in_clause to lista wartości do sekcji kolumn (u nas ‘a’, ‘b’, ‘c’, ‘k’, j’)
Klauzula pivot nie informuje o tym wprost, ale do sekcji wierszy trafią wszystkie kolumny pierwotnego zapytania nie wymienione w nawiasach po słowie PIVOT Jeśli zatem w tabeli jest więcej kolumn niż te, które aktualnie chcemy analizować trzeba je ograniczyć stosując WITH (najczęstsza forma stosowania PIVOT w 11g):
WITH dane AS (SELECT kol1, kol2, kol3 FROM tabela)
SELECT ...
FROM dane
PIVOT
( pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
A więc w naszym przypadku:
WITH dane AS (SELECT NUM, KAT FROM tabela)
SELECT *
FROM dane
PIVOT
( COUNT(*)
FOR KAT
IN ('a', 'b', 'c', 'j', 'k' )
)
Problemem w obu przypadkach jest konieczność znajomości listy wartości jakie trafią do sekcji kolumn. Da się ten problem obejść, ale nie zwyczajnym zapytaniem SQL.
Wymagania specjalne
Zamiana tego:
NUM KAT 1 a 1 b 1 c 2 k 3 j 3 a
Na:
NUM kol1 kol2 kol3 1 a b c 2 k 3 j a
wymaga komentarza. Otóż tutaj mamy do czynienia z pewnym klasycznym przypadkiem w którym problem nie jest relacyjny (z punktu widzenia teorii baz danych), bowiem to, że kol1 dla wartości 1 to a a kol2 to b wynika z kolejności danych wejściowych. Unikamy jak ognia takich przypadków, bo w praktyce rodzą błędy bardzo trudne do wykrycia. Da się jednak wymusić i takie zachowanie w Oracle. Po pierwsze numerujemy sobie wiersze:
SELECT NUM, KAT, Row_Number() over (PARTITION BY NUM ORDER BY KAT) FROM tabela;
a następnie trochę modyfikujemy sposób uzyskania wyniku z 10g:
WITH dane AS (SELECT NUM, KAT, Row_Number() over (PARTITION BY NUM ORDER BY KAT) as rn FROM v)
SELECT NUM,
MIN(CASE WHEN RN = 1 THEN KAT END),
MIN(CASE WHEN RN = 2 THEN KAT END),
MIN(CASE WHEN RN = 3 THEN KAT END),
MIN(CASE WHEN RN = 4 THEN KAT END),
MIN(CASE WHEN RN = 5 THEN KAT END),
MIN(CASE WHEN RN = 6 THEN KAT END),
MIN(CASE WHEN RN = 7 THEN KAT END),
MIN(CASE WHEN RN = 8 THEN KAT END),
MIN(CASE WHEN RN = 9 THEN KAT END)
FROM dane
GROUP BY NUM;
Niestety nadal musimy znać liczbę kolumn w wyniku – tutaj założyliśmy, że będzie ich 9. Funkcja MIN użyta w zapytaniu, może zostać zamieniona ma MAX – nie ma to znaczenia gdyż nie będzie 2 wartości o takim samym RN i NUM.
