[SQL] Rijen naar kolommen

[Verwijderd]

Hoi.

Ik wil met 2 tabellen en een koppeltabel een matrix genereren, waarin de eerste tabel waarden over de verticale as vult en de tweede tabel de horizontale as. De koppeltabel bepaalt welke bool-waarden in de cellen komen.

onderstaand een voorbeeld met welke kinderen welke groenten lusten

database

code:

KIND {
    1: JAN
    2: PIET
    3: KIM
    4: LISA
}

LUST {
    1, 1;
    1, 4;
    2, 3;
    3, 1;
    3, 2;
    3, 3;
    3, 4;
    4, 1;
    4, 3;
}

GROENTE {
    1: PREI;
    2: SPRUITJES;
    3: BROCCOLI;
    4: ANDIJVIE;
}

gewenst resultaat

code:

            PREI SPRUITJES BROCCOLI ANDIJVIE
JAN          T                         T
PIET                           T
KIM          T        T        T       T
LISA         T                 T

met iets dergelijks krijg je wel de juist info uit de database, maar niet op de manier waarmee ik er verder mee wil in de code

code:

select * from kind
inner join lust on kind.id = lust.kind
inner join groente on groente.id = lust.groente

wie kan me in de juiste richting wijzen?

[MueR]

Drie losse queries doen? Eentje die alle groenten ophaalt en in een array propt. Vervolgens alle kinderen ophalen, die in een array proppen. Vervolgens een array $data, met de IDs van de kinderen als key, per kind daarin een array met groenten als key en een 0 of false als value. Dan kan je daarna met je query vanuit de koppeltabel de juiste waarden op true zetten. Ook wat performanter met grotere happen data.

[.oisyn]

Ik zou de aanpak van MueR ook aanhouden, maar wat je feitelijk conceptueel wilt is juist een cross join tussen KIND en GROENTE, en geen inner join. Je wilt namelijk een matrix, dus waarbij elke combinatie van elementen voor moet komen. Vervolgens wil je een left join op die matrix en de LUST tabel om uit te vinden of een kind een groente lust of niet.

SQL:

SELECT CONCAT(k.naam, IF(l.kind_id IS NULL, ' lust geen ', ' lust '), g.naam)
FROM KIND k
CROSS JOIN GROENTE g
LEFT JOIN LUST l ON k.kind_id=l.kind_id AND g.groente_id=l.groente_id
ORDER BY k.kind_id, g.groente_id

Output:

JAN lust PREI
JAN lust geen SPRUITJES
JAN lust geen BROCCOLI
JAN lust ANDIJVIE
PIET lust geen PREI
PIET lust geen SPRUITJES
PIET lust BROCCOLI
PIET lust geen ANDIJVIE
KIM lust PREI
KIM lust SPRUITJES
KIM lust BROCCOLI
KIM lust ANDIJVIE
LISA lust PREI
LISA lust geen SPRUITJES
LISA lust BROCCOLI
LISA lust geen ANDIJVIE

[ Voor 61% gewijzigd door .oisyn op 14-10-2009 14:20 ]

[_js_]

Sommige varianten van SQL ondersteunen een pivot opdracht waarmee dat te doen is. Ook kun je dit soort zaken op jouw manier weergeven in Excel of iets dergelijks.

[labee]

Als je de mogelijkheid hebt om een deel van de query te genereren in code dan kun je de groente tabel gebruiken om de onderstaande query te generen (even uit mijn hoofd).

code:

select 
  kindid,
  kindnaam,
  max(case when groenteid = 1 then 'X' end) as Prei,
  max(case when groenteid = 2 then 'X' end) as Spruitjes,
  max(case when groenteid = 3 then 'X' end) as BROCCOLI,
  max(case when groenteid = 4 then 'X' end) as ANDIJVIE
from Kind, Lust
where kind.kindid = lust.kindid
group by groenteid
order by kindnaam

[Verwijderd]

Wat ik nou zo jammer vind is dat er na twee prima replies nog zulke diarree moet volgen...

[labee]

Waarom is die oplossing niet goed?
Voor oplossing 1 moet er nog een hoop gecodeerd worden,
Oplossing 2 geef niet was TS wil hebben.

[ErikKo]

Rijen naar kolommen. Het eerste waar ik aan moet denken is het woord 'transponeren'.
Een vlugge google levert de volgende resultaten op:
http://www.nettakeaway.com/tp/index.php?id=284
http://forums.devx.com/showthread.php?t=160021

Misschien dat je daar iets aan hebt.

[NMe]

labee schreef op vrijdag 16 oktober 2009 @ 21:33:
Waarom is die oplossing niet goed?
Voor oplossing 1 moet er nog een hoop gecodeerd worden,
Oplossing 2 geef niet was TS wil hebben.

Een cross join geeft perfect wat de topicstarter wil hebben. Dat .oisyn het niet helemaal voorkauwt door een matrix te bouwen wil niet zeggen dat het niet kán.

Jouw oplossing is daarentegen verschrikkelijk vies omdat je je query aan kan gaan passen voor elk record dat erbij komt in de groententabel. Da's zo'n beetje de grootste onderhoudsnachtmerrie die je als ontwikkelaar voor jezelf kan creeëren.

Ik ben overigens wel redelijk benieuwd waarom .oisyn wel de cross join kent maar intussen toch voor drie losse queries zou kiezen, trouwens. Zitten er nadelen aan die ik over het hoofd zie?

[_js_]

Bandbreedte gebruik tussen de SQL server en de publicatieserver is volgens mij het enige nadeel.

Trouwens, TS gaf in zijn post ook al aan dat hij wel de relevante gegevens uit zijn database kon krijgen, maar niet in het formaat dat hij wou, daarom heb ik (en waarschijnlijk labee voor dezelfde reden) ervoor gekozen om naast de juiste oplossingen ook de oplossing die TS zocht aan te bieden.

[ Voor 60% gewijzigd door _js_ op 17-10-2009 03:52 ]

[NMe]

_js_ schreef op zaterdag 17 oktober 2009 @ 03:50:
Bandbreedte gebruik tussen de SQL server en de publicatieserver is volgens mij het enige nadeel.

Trouwens, TS gaf in zijn post ook al aan dat hij wel de relevante gegevens uit zijn database kon krijgen, maar niet in het formaat dat hij wou, daarom heb ik (en waarschijnlijk labee voor dezelfde reden) ervoor gekozen om naast de juiste oplossingen ook de oplossing die TS zocht aan te bieden.

Als je die select zo aanpast dat je niet een string ophaalt maar drie verschillende velden dan heb je het zo'n beetje op de meest simpele manier voor elkaar:

SQL:

SELECT k.naam, IF(l.kind_id IS NULL, 0, 1) AS voorkeur, g.naam

Vervolgens is alles wat je in je code doet checken of er een 1 of een 0 staat terwijl je erdoorheen loopt en afhankelijk daarvan je tabel vullen.

[Cyphax]

NMe schreef op zaterdag 17 oktober 2009 @ 03:36:
Jouw oplossing is daarentegen verschrikkelijk vies omdat je je query aan kan gaan passen voor elk record dat erbij komt in de groententabel. Da's zo'n beetje de grootste onderhoudsnachtmerrie die je als ontwikkelaar voor jezelf kan creeëren.

Alsof elke database onderhevig is aan frequente veranderingen. Daarnaast kun je zo'n query makkelijk genereren, dan ben je ook van je onderhoudsprobleem af. Het een vrij gebruikelijke manier van pivotten zelfs.
Vanaf SQL Server 2005 kan je PIVOT dan gebruiken, dat performt beter.

Als het niet hoeft zou ik het pivotten overlaten aan je code, om je query wat eenvoudiger te houden. Als dat geen optie is zou ik een query genereren die op labee's query lijkt en die uitvoeren (met een stored procedure) of als je SQL Server gebruikt gaan kijken naar PIVOT (hoewel de syntax daarvan lastig is). Die keuze moet je zelf maken.

[NMe]

Cyphax schreef op zaterdag 17 oktober 2009 @ 09:38:
[...]

Alsof elke database onderhevig is aan frequente veranderingen.

Een record toevoegen is toch juist waar een tabel voor bedoeld is. De cross join en de methode van MueR zullen beiden altijd blijven werken, de methode van labee vereist dat je voor elk nieuw record ineens je query moet gaan aanpassen. Ik zou het wel weten.

[.oisyn]

NMe schreef op zaterdag 17 oktober 2009 @ 03:36:
Ik ben overigens wel redelijk benieuwd waarom .oisyn wel de cross join kent maar intussen toch voor drie losse queries zou kiezen, trouwens. Zitten er nadelen aan die ik over het hoofd zie?

Data duplicatie. Je bent geïnteresseerd in n+m records. Met een crossjoin geeft de database je er n*m.