How to Dynamically Pivot Tables in MySQL?

MySQL dynamic pivot: convert rows to columns

In MySQL, you may need a pivot table to convert rows into columns. This is especially useful when you need to represent data in different orientations. Although MySQL does not provide a PIVOT function natively, there are several ways to accomplish this task.

Challenge: Dynamic Pivot Table

Suppose you have three tables: Products, Partners, and Sales. You need to create a table with partners as rows and products as columns, and calculate the sales quantity associated with each combination.

Use CASE statement

One way is to use an aggregate function with a CASE statement. By creating multiple CASE statements, you can calculate sales for each product and group the results by partner name.

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;

Use prepared statements to implement dynamic perspective

For situations where the number of products is unknown and changes dynamically, you can use prepared statements. By dynamically generating SQL queries, you can adjust the number of columns based on available products.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN Product_Name = ''',
      Product_Name,
      ''' THEN 1 END) AS ',
      REPLACE(Product_Name, ' ', '')
    )
  ) INTO @sql
FROM products;

SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' 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');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Restrictions and Notes

Note that GROUP_CONCAT the default byte limit is 1024. If the concatenated string exceeds this limit, you may need to use SET @@group_concat_max_len = 32000; to increase the limit.

The above is the detailed content of How to Dynamically Pivot Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!