Google+ Followers

dinsdag 7 juli 2009

Kolommen ipv rijen.

Je kent het wel. Je hebt een mooie query en die levert fraaie data op in rijen;

productid - eigenschapnaam - waarde
1 - hoogte - 50
1 - breedte - 100
1 - kleur - roze
2 - hoogte - 14
2 - breedte - 90


... en dan komt er een of andere ontwikkelaar en die moet het in kolommen hebben:

productid - hoogte - breedte - kleur
1 - 50 - 100 - roze
2 - 14 - 90 - NULL


Een oplossing is om de ontwikkelaar in zijn eigen taal tegemoed te treden en het internationale gebaar van vriendschap te geven, maar we hebben nog negen andere vingers om crea mee te zijn.

Hoe crea? Nou heel erg crea.

Het eerste waar je natuurlijk aan denkt is:

SELECT
productid,
CASE WHEN eigenschapnaam='hoogte' THEN waarde END AS hoogte,
CASE WHEN eigenschapnaam='breedte' THEN waarde END AS breedte,
CASE WHEN eigenschapnaam='kleur' THEN waarde END AS kleur


Dat geeft wel kolommen maar nog steeds meerdere rijen met één waarde per rij. Valt dit niet plat te drukken tot één rij per productid? Uiteraard, met GROUP BY. Maar bij GROUP BY moet je altijd aangeven welke van de mogelijke waarden je bedoelt. Dat kan op de simpele manier met MAX(), er vanuit gaan de dat NULL altijd kleiner is dan alle andere waarden.

SELECT
productid,
MAX(CASE WHEN eigenschapnaam='hoogte' THEN waarde END) AS hoogte,
MAX(CASE WHEN eigenschapnaam='breedte' THEN waarde END) AS breedte,
MAX(CASE WHEN eigenschapnaam='kleur' THEN waarde END) AS kleur



Maar wij zijn vandaag crea.

In PostgreSQL kun je zelf aggregaten definieren. Een aggregaat is een functie die iets doet met data uit een kolom over meerdere records. De functie die ik gebruik is GROUP_COALESCE(). De naam doet al wat vermoeden en ja deze functie geeft voor een bepaalde kolom van de recordgroup de eerste waarde die niet NULL is.

SELECT
productid,
GROUP_COALESCE(CASE eigenschapnaam
WHEN 'hoogte'
THEN waarde END) AS hoogte,
GROUP_COALESCE(CASE eigenschapnaam
WHEN 'breedte'
THEN waarde END) AS breedte,
GROUP_COALESCE(CASE eigenschapnaam
WHEN 'kleur'
THEN waarde END) AS kleur;



Waarom een aparte functie? Omdat het kan.