<\/p>\r\n\r\n\r\n\r\n
A pivot table in MySQL with dynamic headers.<\/p>\r\n\r\n\r\n\r\n
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.<\/p>\r\n\r\n\r\n\r\n
+----+---------+---------------+--------+\r\n| id | item_id | property_name | value |\r\n+----+---------+---------------+--------+\r\n| 1 | 1 | color | blue |\r\n| 2 | 1 | size | large |\r\n| 3 | 1 | weight | 65 |\r\n| 4 | 2 | color | orange |\r\n| 5 | 2 | weight | 57 |\r\n| 6 | 2 | size | large |\r\n| 7 | 3 | size | small |\r\n| 8 | 3 | color | red |\r\n| 9 | 3 | weight | 12 |\r\n| 10 | 4 | color | violet |\r\n| 11 | 4 | size | medium |\r\n| 12 | 4 | weight | 34 |\r\n| 13 | 5 | color | green |\r\n| 14 | 5 | weight | 10 |\r\n+----+---------+---------------+--------+\r\n\r\n\r\n+---------+--------+--------+--------+\r\n| item_id | color | size | weight |\r\n+---------+--------+--------+--------+\r\n| 1 | blue | large | 65 |\r\n| 2 | orange | large | 57 |\r\n| 3 | red | small | 12 |\r\n| 4 | violet | medium | 34 |\r\n| 5 | green | NULL | 10 |\r\n+---------+--------+--------+--------+<\/pre>\r\n\r\n\r\n\r\n<\/p>\r\n\r\n\r\n\r\n
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.<\/p>\r\n\r\n\r\n\r\n
This question may be of the following types:<\/p>\r\n\r\n\r\n\r\n
SELECT\r\n item_id,\r\n MAX(IF(property_name = 'color', value, NULL)) AS color,\r\n MAX(IF(property_name = 'size', value, NULL)) AS size,\r\n ...\r\n ...\r\n ...\r\nFROM\r\n properties\r\nGROUP BY\r\n item_id;<\/pre>\r\n\r\n\r\n\r\nAs 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:<\/p>\r\n\r\n\r\n\r\n
SET @sql = NULL;\r\nSELECT\r\n GROUP_CONCAT(DISTINCT\r\n CONCAT(\r\n 'MAX(IF(property_name = ''',\r\n property_name,\r\n ''', value, NULL)) AS ',\r\n property_name\r\n )\r\n ) INTO @sql\r\nFROM properties;\r\nSET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');<\/pre>\r\n\r\n\r\n\r\nAs the result will be created the query:<\/p>\r\n\r\n\r\n\r\n
SELECT\r\n item_id,\r\n MAX(IF(property_name = 'color', value, NULL)) AS color,\r\n MAX(IF(property_name = 'size', value, NULL)) AS size,\r\n MAX(IF(property_name = 'weight', value, NULL)) AS weight\r\nFROM\r\n properties\r\nGROUP BY\r\n item_id<\/pre>\r\n\r\n\r\n\r\n<\/p>\r\n\r\n\r\n\r\n
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:<\/p>\r\n\r\n\r\n\r\n
SET @@group_concat_max_len = 5000;\r\nSELECT GROUP_CONCAT(column_name) FROM table;<\/pre>\r\n\r\n\r\n\r\nThe query is written into variable @sql; now we can execute it with prepared statements:<\/strong><\/p>\r\n\r\n\r\n\r\n
PREPARE stmt FROM @sql;\r\nEXECUTE stmt;\r\nDEALLOCATE PREPARE stmt;\r\n+---------+--------+--------+--------+\r\n| item_id | color | size | weight |\r\n+---------+--------+--------+--------+\r\n| 1 | blue | large | 65 |\r\n| 2 | orange | large | 57 |\r\n| 3 | red | small | 12 |\r\n| 4 | violet | medium | 34 |\r\n| 5 | green | NULL | 10 |\r\n+---------+--------+--------+--------+<\/pre>\r\n\r\n\r\n\r\nScript of creation and filling of the table:<\/p>\r\n\r\n\r\n\r\n
CREATE TABLE properties (\r\n id INT(11) NOT NULL AUTO_INCREMENT,\r\n item_id INT(11) DEFAULT NULL,\r\n property_name VARCHAR(255) DEFAULT NULL,\r\n value VARCHAR(255) DEFAULT NULL,\r\n PRIMARY KEY (id)\r\n);\r\nINSERT INTO properties VALUES \r\n (1, 1, 'color', 'blue'),\r\n (2, 1, 'size', 'large'),\r\n (3, 1, 'weight', 65),\r\n (4, 2, 'color', 'orange'),\r\n (5, 2, 'weight', 57),\r\n (6, 2, 'size', 'large'),\r\n (7, 3, 'size', 'small'),\r\n (8, 3, 'color', 'red'),\r\n (9, 3, 'weight', 12),\r\n (10, 4, 'color', 'violet'),\r\n (11, 4, 'size', 'medium'),\r\n (12, 4, 'weight', 34),\r\n (13, 5, 'color', 'green'),\r\n (14, 5, 'weight', 10);<\/pre>\r\n","protected":false},"excerpt":{"rendered":"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....<\/p>\n","protected":false},"author":1,"featured_media":4193,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[246],"tags":[11,82,7],"class_list":["post-3777","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-database","tag-mysql","tag-sql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/3777","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/comments?post=3777"}],"version-history":[{"count":10,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/3777\/revisions"}],"predecessor-version":[{"id":4190,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/3777\/revisions\/4190"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/media\/4193"}],"wp:attachment":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/media?parent=3777"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/categories?post=3777"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/tags?post=3777"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}