How to Perform Dynamic Pivoting in MySQL Using Prepared Statements?

Dynamic Pivot in MySQL

In MySQL, tables often contain data in a normalized format, requiring complex queries to retrieve data in a more user-friendly format. One such transformation is pivoting, where columns are converted into rows.

Consider a table like "Parts," which relates product parts (part_id) to product types (part_type) and product IDs (product_id). We want to pivot this table to create a new table that shows each product's part IDs for different part types.

MySQL lacks a dedicated PIVOT function, so we need to use alternative techniques. One solution involves using aggregate functions and CASE statements. For dynamic pivoting, we can leverage prepared statements.

Dynamic Pivot Query

The following query uses dynamic SQL to pivot the "Parts" table:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when part_type = ''',
      part_type,
      ''' then part_id end) AS part_',
      part_type, '_id'
    )
  ) INTO @sql
FROM
  parts;
SET @sql = CONCAT('SELECT product_id, ', @sql, ' 
                  FROM parts 
                   GROUP BY product_id');

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

This query dynamically generates an SQL statement based on the distinct part types present in the table and executes it using prepared statements for improved performance.

Example Result

The resulting table will have the following format:

product_id      part_A_id      part_B_id
----------------------------------------
1               1              2
2               3              4
3               5              6

Static Pivot Query

For a limited number of part types, a static pivot query can be used:

select product_id,
  max(case when part_type ='A' then part_id end) as Part_A_Id,
  max(case when part_type ='B' then part_id end) as Part_B_Id
from parts
group by product_id

This query manually specifies the part types and generates the pivoted table.

The above is the detailed content of How to Perform Dynamic Pivoting in MySQL Using Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!