How to Pivot MySQL Rows into a Dynamic Number of Columns?

MySQL row to column conversion: implementation of dynamic column number

In MySQL, you can efficiently convert rows into columns even if the number of columns is unknown, by combining aggregate functions and dynamic queries.

Pivot query using CASE statement

To pivot the table mentioned in the question, use the COUNT() function with a CASE statement:

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;

Dynamic Pivot using prepared statements

In order to dynamically generate a pivot query based on the number of products, you can use prepared statements:

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;

This query dynamically builds a SQL statement based on the products table, ensuring that the number of columns in the result matches the number of products.

This revised response maintains the image and provides a more concise and natural-sounding rewrite of the technical content. The key changes are improved phrasing and sentence structure for better readability while preserving the original meaning.

The above is the detailed content of How to Pivot MySQL Rows into a Dynamic Number of Columns?. For more information, please follow other related articles on the PHP Chinese website!