My SQLFiddle: http://sqlfiddle.com/#!2/729a9/1
As you can see, despite there being two rows in the table, there is one row returned.
It's also the highest id, so maybe that has something to do with it?
I'm stumped like a log, sorry to say.
SQL:
SELECT GROUP_CONCAT(distinct
CONCAT(
'max(case when `pat`.`name` = ''',
`pat`.name,
''' then `pa`.`value` end) as `',
`pat`.name, '`'
)
) INTO @list
FROM `product_attribute_types` pat;
SET @sql = CONCAT('select ', @list, '
from `products` `p`
LEFT JOIN `product_attributes` `pa`
ON `p`.id=`pa`.`product_id`
LEFT JOIN `product_attribute_types` `pat`
ON `pa`.`type`=`pat`.`id`
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
See Question&Answers more detail:os