Eav

eav.explain.txt

CREATE TABLE order_info(  
  order_id INT NOT NULL, 
  product_id INT NOT NULL, 
  option_attribute VARCHAR(20), 
  option_value VARCHAR(20), 
  PRIMARY KEY(order_id,product_id,option_attribute,option_value) 
); 
INSERT INTO order_info VALUES  
(1,10,'Colour','Red'),(1,10,'Size','Large'),(1,10,'Sleeve','Short'), 
(2,10,'Colour','Blue'),(2,10,'Size','Small'),(2,10,'Sleeve','Long'); 

How would you report that data with options for each order on one line?

SELECT  
  order_id   AS `Order`, 
  product_id AS Product, 
  MAX(CASE WHEN option_attribute = 'Colour' THEN option_value END) AS Colour,  
  MAX(CASE WHEN option_attribute = 'Size'   THEN option_value END) AS Size, 
  MAX(CASE WHEN option_attribute = 'Sleeve' THEN option_value END) AS Sleeve 
FROM order_info  
GROUP BY order_id, product_id; 
+-------+---------+--------+-------+--------+ 
| Order | Product | Colour | Size  | Sleeve | 
+-------+---------+--------+-------+--------+ 
|     1 |      10 | Red    | Large | Short  | 
|     2 |      10 | Blue   | Small | Long   | 
+-------+---------+--------+-------+--------+ 

The unmentioned trick is that to write this query, you need the results of ...

SELECT DISTINCT option_attribute FROM order_info; 
+------------------+ 
| option_attribute | 
+------------------+ 
| Colour           | 
| Size             | 
| Sleeve           | 
+------------------+ 

https://www.artfulsoftware.com/infotree/tip.php?id=817