Look at the data you have. You repeat the name of the attribute, which violates the third normal form, as it's an unnecessary data redundancy. You will need to refactor your database structure. Create the following tables:
- attribute (id, attribute_name)
- product_attribute (id, attribute_id, value)
Modify your table so it has this structure: product (id, category, product_attribute_id)
and then you can join product - product_attribute - attribute and group by category, using group_concat for the attributes.
How to get the columns? Simple:
SELECT attribute_name
FROM attributes;
Let's generate some stuff form this:
SELECT CONCAT('CASE WHEN attribute_name = \'', attribute.attribute_name, '\' THEN attribute.attribute_name ELSE NULL END AS \'', attribute.attribute_name, '\'')
FROM attribute;
So, you will need to use a cursor in order to iterate the results and that will be a SELECT
clause that you generate. Let's see how the text to be generated should look alike:
/*SELECT CLAUSE HERE BASED ON THE INSTRUCTIONS ABOVE*/
FROM attribute
JOIN product_attribute
ON attribute.id = product_attribute.attribute_id
JOIN product
ON product_attribute_id = product.product_attribute_id
GROUP BY category;
and then EXECUTE this text. Yes, it is not straight-forward and yes, you will need to restructure your database, normalize your tables and migrate your data as a prerequisite for this solution, but INMHO keeping your database not normalized would be harmful by itself.