25.07.2021, Vladimír Klaus, navštíveno 1780x

MS Access
MS Office
SQL

Příkaz TRANSFORM, který by se asi měl spíše jmenovat PIVOT slouží k tvorbě křížového dotazu nebo lépe řečeno vytvoření jakési fixní kontingenční tabulky, kterou znáte z MS Excelu nebo, pokud programujete, tak i z řady speciálních komponent jako je TcxPivotGrid apod. Toho lze využít pro tvorbu přehledů nebo statistik.

Začneme jednoduchým seskupovacím dotazem, kdy nás zajímá počet článků dle autorů a dle krajů.

SELECT Clanky.Autor, Clanky.Kraj, COUNT(Clanky.ID) AS Celkem
FROM Clanky
GROUP BY Clanky.Autor, Clanky.Kraj

Získáme tuto tabulku:

MS Access - Příkaz TRANSFORM/PIVOT, obr. 1

Výsledek je dostačující ale ne moc přehledný - autor se opakuje tolikrát, v kolika krajích něco publikoval. Mnohem lepší by bylo, kdyby v řádcích byli pouze Autoři a informace o Krajích by byly použity pro sloupce. Vlastní hodnoty v tabulce by pak byly opět počty dle autorů a krajů.

Dotaz tedy upravíme tak, že Kraj vyjmeme ze seskupování a přesuneme do nového příkazu PIVOT Clanky.Kraj. Tím přesně říkáme - z hodnot ve sloupci Kraj vytvoř nové "virtuální sloupce". A na začátek přidáme ten hlavní příkaz TRANSFORM Count(Clanky.ID) AS Pocet, kterým určíme, co bude v jednotlivých buňkách výsledné tabulky - tedy počet ID (počet článků).

TRANSFORM COUNT(Clanky.ID) AS Pocet
SELECT Clanky.Autor, COUNT(Clanky.ID) AS Celkem
FROM Clanky
GROUP BY Clanky.Autor
PIVOT Clanky.Kraj

Výsledek je mnohem zajímavější:

MS Access - Příkaz TRANSFORM/PIVOT, obr. 2

Další zajímavostí je, že díky tomuto příkazu dojde ke "sloučení" Autorů - nerozlišuje se velikost písmen. Pěkně je to vidět hned na první řádce, kde není autor vyplněn nebo u ČTK/čtk.

Celý dotaz můžete dále zpřesňovat - třeba podmínkou na Autora (pozor, abyste dali WHERE na správné místo) a případně si i určit, že vás zajímají jen vyjmenované kraje - tedy ve výsledku budou jen tyto tři virtuální sloupce.

TRANSFORM COUNT(Clanky.ID) AS Pocet
SELECT Clanky.Autor, COUNT(Clanky.ID) AS Celkem
FROM Clanky
WHERE Autor LIKE 'j*'
GROUP BY Clanky.Autor
PIVOT Clanky.Kraj IN ('Liberecký a Ústecký kraj', 'Kraj Vysočina', 'Praha')

Na výsledku je vidět, že omezení na některé kraje je opravdu jen na úrovni zobrazených sloupců, protože v normálním sloupci Celkem jsou sečteny všechny články daného autora - agregační funkce COUNT pracuje nad hlavním SELECTem s ohledem na GROUP BY. A teprve tento výsledek je dále předáván do TRANSFORM/PIVOT.

MS Access - Příkaz TRANSFORM/PIVOT, obr. 3

Ukázky v tomto článku patří mezi jednodušší, protože jsem si vystačil pouze s jednou tabulkou. Není ale problém tabulky spojovat a vytvářet tak mnohem sofistikovanější přehledy. Jen musíte dát pozor na to, aby těch virtuálních sloupců nebylo příliš mnoho - MS Access má počet sloupců v (jakémkoliv dotazu) omezen na 255.

U SQL SERVERu existuje podobná funkcionalita také, jen má zcela jinou syntaxi.

Zdroje:

Nahoru