mysql query pivot table example

Pivot tables are used to simplify extensive data in order to make it easier to read and understand. To pivot table, an aggregate is used against a set of data to distribute multiple rows of a single column into a single row with multiple columns.

CREATE TABLE properties (
  id INT(11) NOT NULL,
  item_id INT(11) DEFAULT NULL,
  prop VARCHAR(255) DEFAULT NULL,
  value VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO properties VALUES 
  (1, 1, 'color', 'blue'),
  (2, 1, 'size', 'large'),
  (3, 2, 'color', 'orange'),
  (4, 3, 'size', 'small'),
  (5, 4, 'color', 'violet'),
  (6, 5, 'color', 'green');

Pivot table output:

SELECT
  item_id,
  MAX(IF(prop = 'color', value, NULL)) AS color,
  MAX(IF(prop = 'size', value, NULL)) AS size
FROM
  properties
GROUP BY
  item_id;
+---------+--------+-------+
| item_id | color  | size  |
+---------+--------+-------+
|       1 | blue   | large |
|       2 | orange | NULL  |
|       3 | NULL   | small |
|       4 | violet | NULL  |
|       5 | green  | NULL  |
+---------+--------+-------+