How to convert rows to column in mysql


MySQL pivot table with dynamic headers.

Let’s assume we have a table of properties (a properties table) – ‘properties’ (script of its creation is given 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     |
+---------+--------+--------+--------+

As is generally known (as it is known), in MySql there is no function of automatically table transformation. Surely we can use some program (tool), which is able to connect to MySql and execute data transformation. But in this case we want to do it manually, and we have one variant – to write a query which will execute the data rotation in the table.

This query can have such type as:

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 – for each value `property_name` we need to do definite actions. It can be easy when the types of properties do not change. But what we should do if the values of properties in the column `property_name` often change or complement with the new ones? In this case we would change the query every time. In this case the algorithm of dynamic construction of this query can help us, this algorithm has to read all possible values of the column `property_name`, and on its basement create a query. The algorithm of query construction is:

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

(the query is formatted additionally for readability)

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);

http://buysql.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: