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.
|
=> |
|
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 ', CONCAT("`", 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);
