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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…