Skip to content

Instantly share code, notes, and snippets.

@E1101
Created July 17, 2019 11:07
Show Gist options
  • Save E1101/1e2d62ecaf85a7ea2601edf48816da2c to your computer and use it in GitHub Desktop.
Save E1101/1e2d62ecaf85a7ea2601edf48816da2c to your computer and use it in GitHub Desktop.
Eav
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 |
+------------------+
@E1101
Copy link
Author

E1101 commented Jul 17, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment