mysql - Pivoting table only returns 1 row

Question

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;

Answer 1

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.