Example Analysis of MySQL PivotTable

This article mainly shows you the "sample analysis of MySQL PivotTable", which is easy to understand and clear. I hope it can help you solve your doubts. Let me lead you to study and learn the article "sample Analysis of MySQL PivotTable".

I have a product parts list like this:

Part

Part_id part_type product_id--1 A 12 B 13 A 24 B 25 A 36 B 3

I want a query that returns the following table:

Product_id part_A_id part_B_id--1 1 22 3 43 5 6

In practice, there will be millions of product components

Best answer

Unfortunately, MySQL does not have PIVOT functionality, but you can model it using aggregate functions and CASE statements. For dynamic versions, you need to use prepared statements:

SET @ sql = NULL;SELECT GROUP_CONCAT (DISTINCT CONCAT ('max (case when part_type ='', part_type,''' then part_id end) AS part_','_id')) INTO @ sqlFROM parts;SET @ sql = CONCAT ('SELECT product_id,',@sql,' FROM parts GROUP BY product_id'); PREPARE stmt FROM @ sql;EXECUTE stmt;DEALLOCATE PREPARE stmt

If you have only a few columns, you can use the static version:

Select product_id,max (case when part_type ='A 'then part_id end) as Part_A_Id,max (case when part_type =' B' then part_id end) as Part_B_Idfrom partsgroup by product_id is all the content of the article "sample Analysis of MySQL PivotTable". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!