[MySQL] Query rows naar columns

[Verwijderd]

Weet iemand hoe je met mysql rows naar columns kan krijgen via een query of een andere weg?

Data ziet er nu als volgt uit:

| id | object_id | item_id | val |
| 1 | 1 | 1 | Jan |
| 2 | 1 | 2 | van der |
| 3 | 1 | 3 | Meer |
| 4 | 1 | 4 | hovenstraat 32 |
| 5 | 1 | 5 | 4536 AB |
| 6 | 2 | 1 | Klaas |
| 7 | 2 | 2 | de |
| 8 | 2 | 3 | Vries |
| 9 | 2 | 4 | molenstraat 63 |
| 10 | 2 | 5 | 7823 HK |

Gewenste resultaat:

| Jan | van der | Meer |
| Klaas | de | Vries |

Dus ik heb enkel voornaam tussenvoegsel en achternaam nodig.
Dit is voor een lijstoverzichtje in een python programma.

Ik heb gezocht en kon alleen een query vinden
die wel de waardes in een column zet maar
niet naast elkaar. Zie hieronder:

CREATE VIEW crosstab AS
SELECT max(if(item_id=2,val,' ')) as 'naam',
max(if(item_id=3,val,' ')) as 'tussenvoegsel',
max(if(item_id=4,val,' ')) as 'achternaam'
FROM mab_db.dat
GROUP BY id;

Andere oplossingen zijn ook welkom.
Alvast erg bedankt voor het meedenken.

[Voutloos]

Misschien en beetje flauw antwoord, maar het is wel het beste antwoord: Je hebt geen speciale query of truc nodig, wat jij nodig hebt is een beter datamodel.

In hoeverre is het datamodel aan te passen?

[Verwijderd]

Het is een kruistabel en het datamodel zit als volgt in elkaar.
Twee tabellen te weten items en data
items ziet er zo uit:

item_id, item_name, item_desc
bijvoorbeeeld
1,fname,voornaam
2,prefix,tussenvoegsel
3,lname,achternaam
etc

data ziet er zo uit:
id,object_id,item_id,val
bijvoorbeeld
1,1,1,Ronald
2,1,2,de
3,1,3,vries

Dit heb ik zo gedaan, omdat je dan oneindig veel telefoonnummers, adressen, emailadressen etc. kan koppelen aan 1 object. En je kan er makkelijk items aan toevoegen.

Zover was ik gekomen in ieder geval, maar andere ideeen zijn altijd welkom

[ Voor 15% gewijzigd door Verwijderd op 13-03-2007 10:01 ]

[NMe]

Dat je oneindig veel telefoonnummers aan een naam wil koppelen snap ik nog wel, maar waarom zou je de naam zelf ook zo opslaan? Je datamodel rammelt, en dat kun je beter aanpassen dat eromheen werken.

[Janoz]

Heel leuk bedacht, maar zoals je misschien al ziet heb je een enorme bak werk eraan en moet je alsnog je database aanpassen als je een kolom erbij hebt (je moet immers je view aanpassen)

Het lijkt me handiger om gewoon grotere entiteiten te nemen. Voornaam achternaam en tussenvoegsel heb je er sowieso maar 1 van. Die zet je er rechtstreeks in. Een adres is ook nooit los van plaats of postcode. Dat houd je ook bij elkaar. Je hebt gelijk dat er mogelijk meerdere adressen zouden kunnen zijn (woon, werk, factuur, aflever enz), maar dit los je op door het adres in een adres tabel (met de kolommen straat, huisnummer, postcode, plaats, state?, land) en hier vervolgens een 1:N koppeling tussen te maken. Hetzelfde geld voor telnummer en email. Eventueel voeg je een kolom toe waarin je aangeeft wat voor entiteit het is (thuis telefoon, mobiel, mobiel zakelijk enz enz)

[Voutloos]

Bijkomend voordeel is ook dat je met passende datatypes kan werken, want nu gooi je werkelijk alles in iets wat wellicht een varchar(255) of Text kolom is.

Je datamodel is te generiek. Ja, je kan er praktisch alles wel mee opslaan, maar er is dus een reden dat je zelf tabellen kan definieren en het DBMS je niet dwingt om alles met 2 van dergelijke tabellen te doen.

[Dido]

Hoewel er inzichtelijkere datamodellen te verzinnen zijn hoeft dit model niet noodzakelijkerwijs slecht te zijn, natuurlijk. Het is wel een heel theoretische benadering van een praktisch probleem, maar goed.

Een query die teruggeeft wat de ST wil is iets als:

code:

SELECT a.val, b.val, c.val
FROM   tabel a
JOIN   tabel b on a.object_id = b.object_id
JOIN   tabel c on a.object_id = c.object_id
WHERE  a.item_id = 1
AND    b.item_id = 2
AND    c.item_id = 3;

[Voutloos]

Sorry, datamodel is imo wel gewoon slecht. Met niet al te veel data zal performance gruwelijk instorten en geen index welke je query echt kan helpen.

Mbt het reeds genoemde datatype puntje: Dit ontwerp staat het toe om in een handomdraai de eigenschap Geboortedatum toe te voegen. Leuke feature, maar wat heb je dan precies? Een veld met het verkeerde datatype en de date/time functions zijn niet praktisch bruikbaar.

[Verwijderd]

Dido bedankt voor je query! Zal alleen even moeten kijken of het datamodel inderdaad wel ok is.
Janoz ik had hiervoor ook een ander datamodel, maar zat een beetje met het volgende probleem:
als je een tabel maakt met
prive telefoon, prive mobiel, prive fax, zakelijke telefoon, zakelijke mobiel, zakelijke fax
en je wilt vervolgens een extra telefoonnummer aan een persoon voegen
dan krijg je een extra row met in dit geval 5 lege velden.

In mijn huidige datamodel is elke row volledig benut.
Nou weet ik niet hoe mysql omgaat met alles en
is jou idee misschien voor het systeem efficiënter omdat je
dan datatypes - lengtes kan aangeven.

Voordat ik alles opnieuw helemaal om ga gooien
wil ik graag zeker weten dat ik nu het juiste model ga gebruiken
en de rest van het programma daarop kan gaan bouwen.

Jou idee is dus
tabel object (oid, naam, voornaam, tussenvoegsel, achternaam, geboortedatum)
tabel adres (aid, oid, straat, nummer, postcode, plaats, land)
tabel telefoon (tid, oid, ptelefoon, pfax, pmobiel, ztelefoon, zfax, zmobiel)
tabel email (eid, oid, pemail, zemail)
tabel url (uid, oid, purl, zurl)
etc. etc.

p = prive en z = zakelijk

klopt dat en is dat het beste of kan het nog beter..?
haha sorry maar ik wil echt niet nog vaker alles opnieuw en het nu goed doen.

[P.O. Box]

Verwijderd schreef op dinsdag 13 maart 2007 @ 11:29:
Dido bedankt voor je query! Zal alleen even moeten kijken of het datamodel inderdaad wel ok is.
Janoz ik had hiervoor ook een ander datamodel, maar zat een beetje met het volgende probleem:
als je een tabel maakt met
prive telefoon, prive mobiel, prive fax, zakelijke telefoon, zakelijke mobiel, zakelijke fax
en je wilt vervolgens een extra telefoonnummer aan een persoon voegen
dan krijg je een extra row met in dit geval 5 lege velden.

In mijn huidige datamodel is elke row volledig benut.
Nou weet ik niet hoe mysql omgaat met alles en
is jou idee misschien voor het systeem efficiënter omdat je
dan datatypes - lengtes kan aangeven.

Voordat ik alles opnieuw helemaal om ga gooien
wil ik graag zeker weten dat ik nu het juiste model ga gebruiken
en de rest van het programma daarop kan gaan bouwen.

Jou idee is dus
tabel object (oid, naam, voornaam, tussenvoegsel, achternaam, geboortedatum)
tabel adres (aid, oid, straat, nummer, postcode, plaats, land)
tabel telefoon (tid, oid, ptelefoon, pfax, pmobiel, ztelefoon, zfax, zmobiel)
tabel email (eid, oid, pemail, zemail)
tabel url (uid, oid, purl, zurl)
etc. etc.

p = prive en z = zakelijk

klopt dat en is dat het beste of kan het nog beter..?
haha sorry maar ik wil echt niet nog vaker alles opnieuw en het nu goed doen.

nou, ik denk janoz meer zoiets bedoelt voor telefoon, email en url:

tabel telefoon (tid, oid, type, nummer) (type = telefoon / fax / mobiel / etc)
tabel (email (eid, oid, type, address) (type = pemail / zemail)
etc...

[TheRookie]

Ik zou 't volgende doen:
tabel telefoon:
tid, oid, ttype, tsoort, tnummer

waarbij ttype: P(rive),Z(akelijk) en tsoort: F(ax),M(obiel), etc...

[ Voor 2% gewijzigd door TheRookie op 13-03-2007 11:39 . Reden: niet 2x ttype ]

[Vunzz]

TheRookie schreef op dinsdag 13 maart 2007 @ 11:38:
Ik zou 't volgende doen:
tabel telefoon:
tid, oid, ttype, tsoort, tnummer

waarbij ttype: P(rive),Z(akelijk) en tsoort: F(ax),M(obiel), etc...

Daarbij moet je dus met een ENUM of SET datatype werken. Dat zijn datatypes die alleen vooraf vastgestelde waardes kunnen hebben.

[TheRookie]

moet niet; beter van wel

Je kan ook een char(1) of iets dergelijks gebruiken

[Verwijderd]

Het zelfde priciepe in feite, maar dan meer uitgesplitst.
Inderdaad wel een beter plan dan me eerste opzet.

Ik zal het het nog op me inwerken laten werken allemaal.
Maar alvast bedankt voor de reacties!

[TheRookie]

Verwijderd schreef op dinsdag 13 maart 2007 @ 12:22:
Het zelfde priciepe in feite, maar dan meer uitgesplitst.

Een ander woord voor uitgesplitst in deze context is denk ik genormaliseerd

[Verwijderd]

Mee eens!

[farlane]

Het datamodal is heier wat vergezocht, maar als je iets hebt als bijvoorbeeld een produkt daabase waarbij de gebruiker custom eigenschappen moet kunnen aanmaken kom je er niet omheen.