[SQL] Hoe een crosstabel maken in mySQL?

[anoniem]

Is het mogelijk om een cross-tabel te maken, zoals in Access? In Access gebruikt men TRANSFORM en PIVOT, waardoor gegevens kolomnamen worden. Bijv.: [i:391c126d30]TRANSFORM Count(customers.customer_id) SELECT customers.visit_postcode FROM customers GROUP BY customers.visit_postcode PIVOT customers.visit_city;[/i:391c126d30] Ik kan bij mySQL geen TRANSFORM\PIVOT of soortgelijke functie vinden. Ook heb ik gezocht op dit forum of dit probleem al een keer is voorgekomen, maar heb niets kunnen vinden. Is dit cross-table überhaut mogelijk in mySQL en zo ja, hoe realiseer je dit?

[anoniem]

Nog geen antwoord van iemand :cry: . Waarschijnlijk is mijn vraag niet helemaal duidelijk... Ik zal het proberen beter uit te leggen. Stel ik heb de volgende tabellen: [code:1:5e9c149c0c]KLANT KLEUREN ---------- ---------- |id|naam | |id|kleur| ---------- ---------- |1 |piet | |1 |blauw| |2 |jan | |1 |rood | |3 |klaas| |2 |geel | ---------- |2 |rood | |2 |geel | |3 |geel | ---------- [/code:1:5e9c149c0c] Ik wil dan dmv een SQL instructie het volgende krijgen: [code:1:5e9c149c0c]----------------------- |naam |blauw|rood|geel| ----------------------- |piet | 1| 1| 0| | jan | 0| 1| 2| |klaas| 0| 0| 1| ----------------------- [/code:1:5e9c149c0c] In Access zou je de volgende SQL-string gebruiken: TRANSFORM Count(kleuren.id) SELECT klant.naam FROM klant INNER JOIN kleuren ON klant.id = kleuren.id GROUP BY klant.naam PIVOT kleuren.kleur; Maar hoe doe je dit in MySQl, als dit uberhaubt kan? Of moet je dit doen door meedere SELECTQUERY's (elke keer bij een row uitvoeren), maar dit zorgt voor veel overhead en dat vermijd ik liever...

[anoniem]

Bij mijn weten bestaat deze functionaliteit niet in mysql. En als je het na zou willen bouwen kom je eigenlijk niet onder het gebruik van sub-selects en stored procedures uit. En beiden worden nog niet of niet volledig ondersteund in de versie 3 (versie 4 wel dacht ik maar dat zal je zelf even na moeten zoeken op de site). M.a.w. je ontkomt waarschijnlijk niet aan het gebruik van meerdere queries vanuit je script.

[anoniem]

Jammer, dat het (nog) niet mogelijk is :x . Ik heb het nu op onderstaande manier opgelost waarbij hij eerst de kolomkoppen ophaalt en daarna bij elke 'cel' een SELECT-query uitvoert. [code:1:da58cbad42]<?php /* koppeling met database */ ... /* kolomtitels genereren */ $kleuren="SELECT DISTINCT kleur.kleur FROM klant INNER JOIN kleur ON klant.id=kleur.id"; $kleuren_query=mysql_query($kleuren); print "<table>\n\t<tr>\n\t\t<th>naam</th>\n"; while ($colum=mysql_fetch_row($kleur_query)) { print "\t\t<th>$colum</th>\n"; $colums=array($colum); } print "\t</tr>\n"; mysql_free_result($kleuren_query); /* rijen genereren */ $klanten="SELECT * FROM klant"; $klanten_query=mysql_query($klanten); while ($row=mysql_fetch_row($klanten_query) { print "\t<tr>\n"; print "\t\t<td>$row['naam']</td>\n"; foreach($colums[] as $kleur) { $aantal_query=mysql_query("SELECT COUNT(kleur) FROM kleur WHERE kleur=$kleur AND id=$row['id']"); print "\t\t<td>mysql_fetch_row($aantal_query)</td>\n"; mysql_free_result($aantal_query); } print "\t</tr>\n"; } print "</table>" mysql_free_result($klanten_query); /* koppeling met database verbreken */ ... ?>[/code:1:da58cbad42] Zijn er snellere (lees:minder belastend voor mijn PC) manieren om dit te doen, die ik over het hoofd zie?

[anoniem]

De performance bottlenecks die ik zie zijn het grote aantal losse queries die uitgevoerd moeten worden op de database en het ophalen van de kleuren. Om bij het laatste te beginnen. Hiervoor moet altijd een full table scan worden uitgevoerd waarover ook nog een DISTINCT wordt uitgevoerd. Beide kunnen ontzettend vertragend werken (zeker als de kleuren table flink gevuld is). Beter zou zijn als je iets als onderstaande (genormaliseerde) structuur zou gebruiken: [code:1:78eaaa3023] KLANT KLANT_KLEUREN KLEUREN ================== ============== ================== klantId int klantId int kleurId int klantNaam varchar kleurId int kleurNaam varchar [/code:1:78eaaa3023] Voortbordurend op je huidige datamodel kan je echter de queries ook beperken tot 1 query voor het ophalen van de kleuren en op basis daarvan onderstaande 2e query samenstellen. Ik heb echter niet getest hoe deze laatste performed: [code:1:78eaaa3023] SELECT KLANT.naam, COUNT(IF(KLEUREN.kleur='blauw',1,null)) AS blauw, COUNT(IF(KLEUREN.kleur='rood',1,null)) AS rood, COUNT(IF(KLEUREN.kleur='geel',1,null)) AS geel FROM KLANT LEFT OUTER JOIN KLEUREN ON KLEUREN.id=KLANT.id GROUP BY KLANT.id [/code:1:78eaaa3023]

[anoniem]

Annie, Met het normaliseren heb je helemaal gelijk en zorgt voor minder vertraging doordat het zoeken op getallen (en indexen) veel sneller gaat. Dit had ik echter al in mijn database gedaan :wink: (ik wou alleen even een snel voorbeeldje neerzetten met klanten en kleuren). De bottlenecks met de losse queries was ik ook al bang voor maar tot mijn verbazing werkt het sneller dan ik gedacht had op mijn database (die een stuk ingewikkelder is door meerdere tabbelen en where-instructies dan het voorbeeld hier). Maar er bevinden zich nog maar 20 records in 'klanten' en 900 records in 'kleuren'. Dit worden er binnen een jaar 3050 records in 'klanten' en gemiddeld 5000 records per week komen er bij in de tabel 'kleuren', dus dan zal de bottlenecks met losse queries wel duidelijk merkbaar zijn :-? . De oplossing van jouw SELECT-query is exact wat ik zocht. Het geeft eigenlijk de zelfde output als een cross-tabel in Access, dus perfect voor mijn doeleinde. En hiermee zijn cross-table dus wel te realiseren in mySQL :D. Het enige probleem is dat de 'kleuren' niet statisch zijn, maar dan kan ik gewoon oplossen door de SQLstring dynamische op te bouwen en dan pas uit te voeren. Dank je wel voor jouw oplossing en reactie! Het heeft mij veel geholpen :D .