How to Create a Dynamic MySQL Pivot Table Query Using Prepared Statements?

Dynamic MySQL Pivot Table Query with Columns Derived from Dynamic Data

The Pivot Table Query

Your requirement is to pivot the data from the product and product_additional tables, with the columns dynamically populated based on the values in the product_additional table. The current query, which uses conditional aggregation with IF(), is not dynamic and requires manual changes with each new field added.

Solution Using Prepared Statements

To achieve dynamic pivoting in MySQL, we can use prepared statements, which allow us to construct a query string at runtime. The following code demonstrates this approach:

-- Initialize @sql variable
SET @sql = NULL;

-- Dynamically create the query string by iterating over distinct `fieldname` values
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.fieldname = ''',
      fieldname,
      ''', pa.fieldvalue, NULL)) AS ',
      fieldname
    )
  ) INTO @sql
FROM product_additional;

-- Concatenate the constructed string with the base query
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 the query
PREPARE stmt FROM @sql;

-- Execute the prepared query
EXECUTE stmt;

-- Deallocate the prepared statement
DEALLOCATE PREPARE stmt;

Execution

This code generates a dynamic query string based on the unique fieldname values in the product_additional table. The string is then used to construct a PREPARE statement, which is executed and then deallocated.

Limitations

Note that the GROUP_CONCAT function has a character limit of 1024 characters. You can increase this limit by setting the group_concat_max_len parameter.

The above is the detailed content of How to Create a Dynamic MySQL Pivot Table Query Using Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!