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;
Firstly: you have had two attributes for the same product_id = 1, change table product_attributes in this way -
INSERT INTO `product_attributes` (`product_id`,`type`,`value`) VALUES
(1,1,'blue'),
(1,2,'shirt'),
(2,1,'green'),
(2,2,'pants');
Then try this one -
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(pat.name = ''', name, ''', pa.value, NULL)) AS ', name
)
) INTO @sql
FROM product_attribute_types;
SET @sql = CONCAT('SELECT pa.product_id, ', @sql, ' FROM product_attributes pa INNER JOIN product_attribute_types pat ON pa.type = pat.id GROUP BY pa.product_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Result:
+------------+-------+-------+
| product_id | color | name |
+------------+-------+-------+
| 1 | blue | shirt |
| 2 | green | pants |
+------------+-------+-------+
Add WHERE filter if needed.
You're missing a GROUP BY clause in @sql, as well as the product ID that the row refers to.
SET @sql = CONCAT('select p.id, ', @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`
GROUP BY p.id
');
FIDDLE
