How can I dynamically pivot data in MySQL without a native PIVOT function?

Dynamic PIVOTING in MySQL

In our relational database, we have a table named "Parts" that holds information about product parts, including their part ID, part type, and associated product ID. The table structure resembles the following:

Parts
--------------------------------------
part_id | part_type | product_id
--------------------------------------
1        | A        | 1
2        | B        | 1
3        | A        | 2
4        | B        | 2
5        | A        | 3
6        | B        | 3

Objective:

Our goal is to construct a query that transforms this data into a pivoted format, resulting in a table with one row for each unique product ID, along with columns representing the corresponding part IDs for "A" and "B" types. The desired output table should look like this:

Product_ID | Part_A_ID | Part _B_ID
----------------------------------------
1           | 1         | 2
2           | 3         | 4
3           | 5         | 6

Solution:

Unfortunately, MySQL lacks a native PIVOT function. However, we can simulate its behavior using a combination of aggregate functions and CASE statements. To create a dynamic version that handles multiple part types, we employ prepared statements:

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 constructs a SQL statement based on the unique part types in the 'Parts' table. It then uses prepared statements to execute the generated SQL statement, efficiently handling potential performance issues with large result sets.

Alternative for a Static Solution with a Limited Number of Columns:

If we have a limited number of part types, we can employ a static version of the query:

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 alternative is recommended when the number of part types is fixed and small.

The above is the detailed content of How can I dynamically pivot data in MySQL without a native PIVOT function?. For more information, please follow other related articles on the PHP Chinese website!