MySQL - převod sloupců v tabulce na řádky

[mirka]

Ahoj, chtěla bych se zeptat, zda existuje v MySQL nějaký snadný postup jak z jedné tabulky udělat druhou tak, aby se mi záznamy v řádcích z té první zaznamenaly do druhé tabulky do jejich sloupců. Zkoušela jsem nějaké návody na netu pod hlavičkou pivot table, ale nemůžu se dobrat uspokojivého výsledku :/ Díky za rady

[Reklama]

[koumes]

Žádný jednoduchý obecný postup neexistuje.

[cosmo]

Obávam sa, že MySQL je hodne vzdialená od tabuľkového kalkulátora, kde sa to rieši pomocou transpozície.

MySQL v základnom princípe funguje inak a nemá automatický mechanizmus, ktorý Vám spraví SELECT(), vytvorí stĺpce podľa hodnôt z riadkov a spraví INSERT().

Veď okrem iného, záznamov (riadkov) môže byť prakticky neobmedzené množstvo a tabuľka, ktorá má po transponovaní 1M stĺpcov a N riadkov by vyzerala divne :-)

Nebude to náhodou nejaká domáca úloha ? Zrejme áno, lebo toto mi naozaj príde ako nejaký pedagogický nezmysel :-D

Ale čisto teoreticky sa to spraviť dá, len nie priamočiaro a jednoducho a s určitými obmedzeniami.

[cosmo]

Ešte dodám, že v relačných databázach sa spravidla zadania normalizujú, aby neurčitý počet hodnôt bol v riadkoch a určitý konečný počet atribútov v stĺpcoch. Tabuľka z reálneho sveta s riadkami a stĺpcami nie vždy zodpovedá rovnako jednoduchej SQL tabuľke.

[Franta]

Tabulka v relační databázi není matice, ale množina záznamů.

[Reklama]

[cosmo]

[franta] Dokonale výstižná odpoveď. V podstate som chcel napísať to isté 

[Rax]

Je s tím spousta psaní, ale jde to takhle nějak

select
(select column1 from table where id=1) as A,
(select column1 from table where id=2) as B,
(select column1 from table where id=3) as C
union all
(select column2 from table where id=1) as A,
(select column2 from table where id=2) as B,
(select column2 from table where id=3) as C
union all
(select column3 from table where id=1) as A,
(select column3 from table where id=2) as B,
(select column3 from table where id=3) as C

[telra]

Bolo by fajn napisat PRECO je to treba, napadaju ma dve moznosti:
1) Cielom je nieco ziskat, a bol zvoleny absolutne zly pristup, robi sa to inak
2) ide naozaj o skolske cvicenie a v tom pripade navrhujem ist do najblizsieho knihkupectva, kupit 500 stranovu bichlu SQL a otrieskat ju vyucujucemu o hlavu

[Tomáš Vondra]

LOL. Někdo se tu zeptá "Jak udělat X?" a dostane se mu odpovědí že vlastně chce úplný nesmysl, případně že "čisto teoreticky sa to spraviť dá" ale jaksi bez informace jak. Přitom pivotace je v mnoha aplikacích celkem běžně používaná záležitost, i když ne vždy se provádí kompletně v DB (někdy je prostě jednodušší to udělat na klientovi nebo v nějaké mezivrstvě). Každopádně pokud se mluví o pivotaci, tak se v drtivé většině případů používá na AGREGOVANÁ data, a to do konečného počtu skupin (např. pevně dané věkové skupiny apod.), takže námitky že neurčitý má být počet řádků jsou mimo ...

Mirko, napište jak vypadají data, proč je chcete pivotovat a proč vám návody dostupné na netu (např. http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf nebo http://en.wikibooks.org/wiki/MySQL/Pivot_table).

[mirka]

proč to chci? ne, opravdu mi to žádný učitel nezadal, protože ve škole se neučím informatiku, nýbrž molekulární biologii...
potřebuju joinovat několik tabulek a jako sjednocující  prvek jsem chtěla použít jméno sloupce (using name_col). tři tabulky jsou orientovány správně, ale jedna je přehozená, tzn. sloupce jsou řádky a řádky jsou sloupci. Abych počítala hodnoty v této tabulce, aby se mi zanamenaly jak potřebuji je prakticky nemožné, nebo zbytečně moc složité. Proto mi přišlo logicky daleko jednodušší, převést sloupce na řádky.
nakonec jsem si poradila jinak, ale docela mě překvapilo, že neexistuje nějaký jednoduchý postup jak převést řádky na sloupce. Logicky mi to nepřipará nijak složitá operace, ale možná se jedná jen o můj dojem a pocit. Každopádně děkuji za všechny komentáře 

[MK]

2 Vondra: Pokud se nekdo zepta, "Mam kilo jablek, jak z toho udelam hruskovy kompot?" Tak se mu taky dostane odpoved, ze se pta na nesmysl a to zcela po pravu.
MySQL se snazi byt relacni databazi a vychazi tedy i z relacni algebry. Krome jineho to znamena, ze adresuji radky jejich hodnotami. Ve slusne databazove spolecnosti ma tabulka primarni klic. Pokud tabulku prevratim, tak primarni klic narvu do jednoho radku. A co pak s tim? Jak budu adresovat radky? A pokud mam ty datove struktury navrzeny tak, ze kdyz to chci joinovat, tak to musim nejdriv otocit, tak bych se mel drzet excelu nez se naucim zaklady relacnich databazi.

[Tomáš Vondra]

Aha, no ale to skutečně zní hodně divoce - můžete uvést příklad (byť zjednodušený) jak ty tři tabulky vypadají (strukturu a pár záznamů)?

Totiž pokud vezmu že ta tabulka má sloupec "name_col" ve kterém jsou nějaká jména (řekněme lidí) a další sloupec, tj. vypadá to např. takhle:

Franta	10
Jirka	20
Honza	1
...	...

tak přeci "pivotováním" vznikne něco takovéhoto:

Franta	Jirka	Honza	...
10	20	1

tj. "cosi" (relace to skutečně není) o dvou řádcích a neurčitém počtu sloupců. A nenapadá mne jak bych to joinoval a co bych tím získal ...

[Tomáš Vondra]

2 Vondra: Pokud se nekdo zepta, "Mam kilo jablek, jak z toho udelam hruskovy kompot?" Tak se mu taky dostane odpoved, ze se pta na nesmysl a to zcela po pravu.
MySQL se snazi byt relacni databazi a vychazi tedy i z relacni algebry. Krome jineho to znamena, ze adresuji radky jejich hodnotami. Ve slusne databazove spolecnosti ma tabulka primarni klic. Pokud tabulku prevratim, tak primarni klic narvu do jednoho radku. A co pak s tim? Jak budu adresovat radky? A pokud mam ty datove struktury navrzeny tak, ze kdyz to chci joinovat, tak to musim nejdriv otocit, tak bych se mel drzet excelu nez se naucim zaklady relacnich databazi.

Tak především z původního postu není zřejmé čeho pisatelka chce dosáhnout - tudíž reakce ve stylu "je to nesmysl" jsou jaksi mimo, neboť jsou založeny na soukromých dedukcích pisatelů. Ano, může se ukázat že dotaz plyne z nepochopení nebo že je špatně navržený datový model (a podle dalšího postu to tak vypadá), ale z toho počátečního postu to skutečně jasné není.

Co se primárních klíčů týká - ano, je takovým pěkným zvykem že tabulky mají primární klíče. Poněkud mi ale uniká jak z toho plyne že pivotace je obecně nesmysl. Když si vezmu např. 2x2 kontingenční tabulku která je výsledkem dotazu SELECT pohlavi, SUM(CASE barva_vlasu WHEN 'blond' THEN 1 ELSE 0 END) AS blond, SUM(CASE barva_vlasu WHEN 'blond' THEN 1 ELSE 0 END) AS not_blond FROM lidi tj. něco jako

pohlaví	blond	not_blond
muž	65	91
žena	41	63

tj. vlastně relaci na doménách [pohlaví, počet osob, počet osob], tak co je tak zavrženíhodného a nelogického na transformaci

barva vlasů	muž	žena
blond	65	41
not blond	91	63

tj. vlastně na relaci na doménách [barva vlasů, počet osob, počet osob]? Mj. si dovoluji poznamenat že obě ty relace mají primární klíče.

[MK]

Pivotace prezentovanych dat neni nesmysl, sam to v BI nastrojich casto pouzivam. Ale je to tak v pripade, ze se jedna o _prezentaci_ dat na _klientu_. Z puvodniho dotazu mi prislo ze tazatelka chce vytvorit novou tabulku v MySQL a do toho presype otocena data z jine tabulky, cituji "postup jak z jedné tabulky udělat druhou tak, aby se mi záznamy v řádcích z té první zaznamenaly do druhé tabulky do jejich sloupců". A to je pak neco spatne, obvykle se jedna o nepochopeni relacnich databazi. Proto na neco takoveho nejsou ani standardni nastroje.