How to Dynamically Generate Columns for Row-to-Column Pivoting in MySQL?

MySQL rotation serial dynamic column generates

Assuming that there are three MySQL tables:

Products table:

Storage product information (ID, name).
• Partners table: Storage partner information (ID, name).
• Sales table: record sales transactions (partners_id, products_id).
• Our goal is to convert the sales data into a table, where the partners are listed in the line and the product is the dynamic column. Pre -aggregation results

Using simple query, we can get a table, which includes the partner name and product name as the column, as well as the number of appearances:

However, this method lacks dynamic columns for the number of variable products.

The dynamic perspective of using the case and the aggregate function

SELECT partners.name AS partner_name, 
       products.name AS product_name, 
       COUNT(*) 
FROM sales
JOIN products ON sales.products_id = products.id
JOIN partners ON sales.partners_id = partners.id
GROUP BY sales.partners_id, sales.products_id

MySQL lacks a dedicated PIVOT function, so we must use a case statement for aggregation query:

This method can dynamically calculate the number of times each partner's product name appears. The dynamic perspective of using the pre -processing statement

For the real dynamic perspective table, we can use the pre -processing statement to generate the SQL query string according to the number of products:

SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = 'Product A' THEN 1 END) AS ProductA,
  COUNT(CASE WHEN pd.product_name = 'Product B' THEN 1 END) AS ProductB,
  COUNT(CASE WHEN pd.product_name = 'Product C' THEN 1 END) AS ProductC,
  COUNT(CASE WHEN pd.product_name = 'Product D' THEN 1 END) AS ProductD,
  COUNT(CASE WHEN pd.product_name = 'Product E' THEN 1 END) AS ProductE
FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name

This method allows us to dynamically generate a list name and aggregate the count of each product. If necessary, remember to adjust the size limit of Group_concat.

The above is the detailed content of How to Dynamically Generate Columns for Row-to-Column Pivoting in MySQL?. For more information, please follow other related articles on the PHP Chinese website!