How to Create Dynamic MySQL Pivot Tables with Dynamic Columns?

Dynamic MySQL Pivot Table Queries with Dynamic Columns

In this scenario, product data is stored in two tables: product and product_additional. The goal is to create a dynamic MySQL query that can adapt to changes in the product_additional table, allowing for the addition of new fieldnames and fieldvalues without the need to manually modify the query.

The provided query effectively retrieves data from both tables and displays it in a tabular format, dynamically populating the size, height, and color columns from the product_additional table. However, this query becomes static if new fields are added dynamically.

To achieve true dynamism, MySQL's Prepared statements can be utilized. These statements allow the construction of dynamic queries at runtime. The code would be as follows:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.fieldname = ''',
      fieldname,
      ''', pa.fieldvalue, NULL)) AS ',
      fieldname
    )
  ) INTO @sql
FROM product_additional;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id, p.name, p.description');

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

This code constructs the dynamic query based on the data in the product_additional table, dynamically adding the necessary columns to the query string. The resulting dynamic query is then executed and the results are displayed.

Additional Considerations:

It's important to note that the GROUP_CONCAT function has a maximum length limit of 1024 characters. If the query requires more characters, the parameter group_concat_max_len can be adjusted to accommodate.

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