Dynamic pivot tables (transform rows to columns)

A pivot table in MySQL with dynamic headers.

Assume we have a table of properties – ‘properties’ (script for its creation is provided below), and we need to do data transformation for the report.

+----+---------+---------------+--------+
| id | item_id | property_name | value  |
+----+---------+---------------+--------+
|  1 |       1 | color         | blue   |
|  2 |       1 | size          | large  |
|  3 |       1 | weight        | 65     |
|  4 |       2 | color         | orange |
|  5 |       2 | weight        | 57     |
|  6 |       2 | size          | large  |
|  7 |       3 | size          | small  |
|  8 |       3 | color         | red    |
|  9 |       3 | weight        | 12     |
| 10 |       4 | color         | violet |
| 11 |       4 | size          | medium |
| 12 |       4 | weight        | 34     |
| 13 |       5 | color         | green  |
| 14 |       5 | weight        | 10     |
+----+---------+---------------+--------+


+---------+--------+--------+--------+
| item_id | color  | size   | weight |
+---------+--------+--------+--------+
|       1 | blue   | large  | 65     |
|       2 | orange | large  | 57     |
|       3 | red    | small  | 12     |
|       4 | violet | medium | 34     |
|       5 | green  | NULL   | 10     |
+---------+--------+--------+--------+

There is no automatic table transformation function in MySql, as is well known (as it is). We can undoubtedly utilize a program (tool) that connects to MySql and performs data manipulation. But in this instance, we want to carry out the data rotation manually, thus we have one option: we can build a query that will do this.

This question may be of the following types:

SELECT
  item_id,
  MAX(IF(property_name = 'color', value, NULL)) AS color,
  MAX(IF(property_name = 'size', value, NULL)) AS size,
  ...
  ...
  ...
FROM
  properties
GROUP BY
  item_id;

As you can see, we need to take specific actions for each ‘property name’ value. When the types of properties do not change, it can be simple. But what if the values of properties in the column ‘property name’ frequently change or are supplemented with new ones? In this case, the query would be different each time. In this case, the dynamic query construction algorithm can assist us; this algorithm must read all possible values of the column ‘property name’ and create a query on its foundation. The query construction algorithm is as follows:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(property_name = ''',
      property_name,
      ''', value, NULL)) AS ',
      property_name
    )
  ) INTO @sql
FROM properties;
SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');

As the result will be created the query:

SELECT
  item_id,
  MAX(IF(property_name = 'color', value, NULL)) AS color,
  MAX(IF(property_name = 'size', value, NULL)) AS size,
  MAX(IF(property_name = 'weight', value, NULL)) AS weight
FROM
  properties
GROUP BY
  item_id

It should be noted that the GROUP CONCAT result length is restricted to the value of the system variable group concat max len, which has a default value of 1024. So, before employing the GROUP CONCAT function, this value can be made higher if you have a lot of columns, for example:

SET @@group_concat_max_len = 5000;
SELECT GROUP_CONCAT(column_name) FROM table;

The query is written into variable @sql; now we can execute it with prepared statements:

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
+---------+--------+--------+--------+
| item_id | color  | size   | weight |
+---------+--------+--------+--------+
|       1 | blue   | large  | 65     |
|       2 | orange | large  | 57     |
|       3 | red    | small  | 12     |
|       4 | violet | medium | 34     |
|       5 | green  | NULL   | 10     |
+---------+--------+--------+--------+

Script of creation and filling of the table:

CREATE TABLE properties (
  id INT(11) NOT NULL AUTO_INCREMENT,
  item_id INT(11) DEFAULT NULL,
  property_name 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, 1, 'weight', 65),
  (4, 2, 'color', 'orange'),
  (5, 2, 'weight', 57),
  (6, 2, 'size', 'large'),
  (7, 3, 'size', 'small'),
  (8, 3, 'color', 'red'),
  (9, 3, 'weight', 12),
  (10, 4, 'color', 'violet'),
  (11, 4, 'size', 'medium'),
  (12, 4, 'weight', 34),
  (13, 5, 'color', 'green'),
  (14, 5, 'weight', 10);