Help with tricky crosstab query

[Jak-S]

Hi, i need a hand with a cross tab query which i just cant work out, ive done crosstabs with numbers before, but i cant figure this out at all. I have this query:

SELECT variations.id, variations.price, options.variable_id, options.name
FROM variations
LEFT JOIN variation_options ON variation_options.variation_id = variations.id
LEFT JOIN options ON options.id = variation_options.option_id
WHERE variations.product_id = 2

Which returns something like this:

variations.id variations.price options.variable_id options.name
-------------------------------------------------------------------------------------
12 6546 1 Super Kingsize
12 6546 2 Mattress Only
12 6546 3 Latex
12 6546 4 Soft
13 321 1 Single
13 321 2 Platform Top 2 Drawer
13 321 3 Standard Sprung
13 321 4 Soft


What i want to go is group this result by the variations.id field so that there are two rows, and then turn the four different options.variable_id values into four columns with the options.name values as the values of those columns, so something like this:

variations.id variations.price variable_1 variable_2 variable_3 variable_4
-------------------------------------------------------------------------------------
12 6546 Super Kingsize Mattress Only Latex Soft
13 321 Single Platform Top 2 Drawer Standard Sprung Soft


Is this possible? Thanks in advance.
Jack

[Jak-S]

Ok, i worked out ONE way to do it, but im not convinced its the best way, this is it:

SELECT variations.id, variations.price,
GROUP_CONCAT(IF(options.variable_id = 1, options.name, NULL)) AS variable_1,
GROUP_CONCAT(IF(options.variable_id = 2, options.name, NULL)) AS variable_2,
GROUP_CONCAT(IF(options.variable_id = 3, options.name, NULL)) AS variable_3,
GROUP_CONCAT(IF(options.variable_id = 4, options.name, NULL)) AS variable_4
FROM variations
LEFT JOIN variation_options ON variation_options.variation_id = variations.id
LEFT JOIN options ON options.id = variation_options.option_id
WHERE variations.product_id = 2
GROUP BY variations.id

Im just not sure that GROUP_CONCAT is the right thing to use. Can it be done any better?

EDIT: What about MAX, that works as well?

Cheers,
Jack

[guelphdad]

Yes I think the GROUP_CONCAT will work for you.