[SQL] Het transponeren van data

[Gomez12]

In een dbase heb ik een artikel-tabel en een kenmerken-tabel. Omdat een artikel meerdere kenmerken kan bevatten en een kenmerk bij meerdere artikelen kan voorkomen (n:n) is dit met een koppeltabel gedaan.

Op de standaard artikel-detail tonen uitvragingen is dit dan ook verdeeld in 2 query's ( haal eerst het artikel op, haal daarna de kenmerken bij dat artikel op ).

Nu ben ik alleen bezig met een export naar excel en dan krijg ik toch de vraag of er niet gewoon 1 regel per artikel kan komen ( dus alle artikelgegevens met alle kenmerken op 1 regel ).

Omdat de kenmerken niet als laatste komen ( en de naam altijd op de 1e regel/kop moet staan ) loop ik tegen een probleempje aan als ik het in 2 query's splits, ik weet bij het schrijven van de kop niet wat het maximale aantal kenmerken is wat gebruikt gaat worden.

Is er een mogelijkheid om via sql alle kenmerknamen ook als kolomkoppen terug te krijgen zonder dat ik van te voren weet hoeveel dit er zijn ( oftewel geen 100 leftjoins want dan kan ik weer 101 kenmerken hebben )

[Voutloos]

Call me stupid, maar dat is toch puur een SELECT kenmerkNaam FROM kenmerken?

Zo nee, post dan maar de structuur + minimale voorbeeld data.

edit:

En eens met hieronder. Met een (bijvoorbeeld) PHP scriptje van 10 regels ben je zo klaar. Bovendien kan je dan eenvoudig je export procedure herhalen.

[ Voor 36% gewijzigd door Voutloos op 06-09-2009 16:53 ]

[GlowMouse]

Waarom zou je dit niet in je export-app regelen?

[Not Pingu]

Welk RDBMS gebruik je? In SQL Server kun je met behulp van een cursor door je kenmerken loopen en deze per artikel combineren in 1 tabel, zodat je dit krijgt:

code:

Artikel1      Kenmerk1, Kenmerk2, Kenmerk3
Artikel2      Kenmerk1,...

edit:
Eens met Glowmouse, dit kun je het beste in code doen, niet met SQL

[ Voor 14% gewijzigd door Not Pingu op 06-09-2009 16:59 ]

[Gomez12]

Ok, voorbeeldtabelstructuur dan :

code:

Artikel : 
- artikelid ( int(11) )
- artikelnr ( varchar (32) )
- Artikelnaam ( varchar ( 128 ) )
- Levid ( int(11) )

Artikelkenmerk :
- artikelid ( int(11) )
- kenmerknr ( int(11) )

Kenmerken :
- kenmerknr ( int(11) )
- Kenmerknaam ( varchar (32) )
- Kenmerkwaarde ( varchar (64) )

Nu wil ik dus idd het volgende eruit halen :
Artikelnr,artikelnaam,kenmerknaam1,kenmerkwaarde1,kenmerknaam2,kenmerkwaarde2,...,levid

Het probleem voor mij zit hem erin dat artikel1 2 kenmerken kan bevatten, terwijl artikel99 15 kenmerken kan bevatten ( plus dat ik eerst nog de kop moet weten )
Kolommen omdraaien ( zodat bijv levid naar voren gaat en ik dus achteraan zou kunnen uitvullen ) is geen oplossing omdat ik dezelfde structuur met bijv afbeeldingen en nog wat andere velden heb.

In code zou het idd met 10 regels op te lossen zijn als het om 10 / 100 artikelen zou gaan ( eerst 2 query's draaien, dan resultaten in code samenvoegen en daarna uiteindelijke samenvoegresultaat exporteren ) enkel gemiddelde export is 300k regels, en csv-export bestand is dan 100M.
Imho niet echt geschikt om in code te gooien en daar trucen mee uit te halen op de totale export set.

Alhoewel nu ik er zo over nadenk, is het natuurlijk wel een mogelijkheid om eerst een : select max(count(kenmerkid)) te doen waardoor ik het aantal maximale aantal kenmerken krijg ( en de kop kan schrijven ) en daarna per artikel een query kan afvuren om de kenmerken op te halen en deze per row weg te schrijven... Maar efficientie hierbij is imho ook 0,0.

Misschien iemand een pseudo code voorbeeldje hoe dit wel efficient te doen is zonder dat de export-functionaliteit een ongelovelijke memory-hog wordt?

[Voutloos]

Gomez12 schreef op zondag 06 september 2009 @ 17:34:
Nu wil ik dus idd het volgende eruit halen :
Artikelnr,artikelnaam,kenmerknaam1,kenmerkwaarde1,kenmerknaam2,kenmerkwaarde2,...,levid

Que? Dan heb je die 1e regel ook niet nodig.

De namen komen op de 1e regel (SELECT naam FROM kenmerken, hatsikidee) en de waardes doe je dan op de regels met data.

offtopic:
Waarom moet men in Excel altijd direct alle structuur overboord gooien?

[Gomez12]

Voutloos schreef op zondag 06 september 2009 @ 17:42:
[...]
Que? Dan heb je die 1e regel ook niet nodig.

De namen komen op de 1e regel (SELECT naam FROM kenmerken, hatsikidee) en de waardes doe je dan op de regels met data.

offtopic:
Waarom moet men in Excel altijd direct alle structuur overboord gooien?

1e regel is benodigd om in excell overzicht te kunnen houden / omdat het hetzelfde formaat is als het import-bestand ( waar weer per kop gekeken wordt welke kolom het is ) / omdat andere partijen het moeten kunnen importeren ( waarbij het makkelijk is om een veldnaam mee te krijgen zodat een systeem wat maar 15 kenmerken aankan ook enkel de 1e 15 kenmerken hoeft te pakken in plaats van dat hij een onbekend aantal kolommen krijgt met onbekende namen...)

Kenmerknaam is geen verplicht veld, sommige kenmerken zeggen voldoende met enkel waardes daarom heet de 1e regel gewoon kenmerknaam1 / kenmerknaam2 etc.

Maar opzich heb je met je ot opmerking me wel een ander leuk idee gegeven, excel 2007 kan xml inlezen / bewerken. Even kijken of dat een beetje werkt, dan kan ik namelijk :

code:

<artikelnr>
        <artikelnaam>
        <kenmerken>
                <kenmerk1>
                        <kenmerknaam>
                        <kenmerkwaarde>

Of iets dergelijks doen...
Gok dat excel een beetje stuk gaat op 300.000 artikelen in xml, maar het is het proberen waard

[Tharulerz]

De limiet van 65.xxx rijen is eruit gehaald in 2007, als je opslaat in xlsx formaat toch.

[Gomez12]

Tharulerz schreef op zondag 06 september 2009 @ 18:19:
De limiet van 65.xxx rijen is eruit gehaald in 2007, als je opslaat in xlsx formaat toch.

De limiet van 65.635 rijen is eruit gehaald, maar 300.000 artikelen opslaan als xml en dit goed weergeven is toch even iets zwaarder dan 300.000 csv regels...

En xlsx formaat zelf aanmaken is onpraktisch omdat het ook in andere systemen automatisch ingelezen moet kunnen worden ( daarom of flat-file of xml ).
Het is meer zoiets van, het moet in excell in te lezen zijn, maar het mag niet enkel in excell ( of windows omgevingen in het algemeen ) te gebruiken zijn...

Of het aantal rijen wel of niet in de client app te gebruiken is is niet ons probleem, daarvoor zit er voor het export-stuk een selectie stuk waar de client zelf de selectie kleiner kan maken ( / meerdere kleine selecties kan opslaan )

Toevoeging : Maar in sql is het dus niet makkelijk te doen om te transponeren? Het algemene advies is toch om het in code te doen?

[ Voor 6% gewijzigd door Gomez12 op 06-09-2009 18:59 ]

[Not Pingu]

Als je nou eens een keer vertelt welk DBMS je gebruikt en in welke taal je van plan zou zijn op te gaan coden, dan kunnen we misschien zinnige antwoorden geven.

Het is weldegelijk mogelijk met alleen SQL (wederom, afhankelijk van je DBMS) en hoewel het wat lastiger is dan coden, kan het kwa deployment enzo een stuk handiger zijn dan gebruikmaken van een export-applicatie.