How to Dynamically Pivot Data in MySQL Using Aggregate Functions and CASE Statements?

Pivoting Data Dynamically with MySQL's Aggregate Functions and CASE Statement

Consider a table of product parts with the following schema:

CREATE TABLE Parts (
    part_id INT NOT NULL,
    part_type CHAR(1) NOT NULL,
    product_id INT NOT NULL,
    PRIMARY KEY (part_id)
);

Sample Data:

INSERT INTO Parts (part_id, part_type, product_id) VALUES
(1, 'A', 1),
(2, 'B', 1),
(3, 'A', 2),
(4, 'B', 2),
(5, 'A', 3),
(6, 'B', 3);

The goal is to pivot this data into a table that represents the product parts as columns:

CREATE TABLE PivotedParts (
    product_id INT NOT NULL,
    part_A_id INT,
    part_B_id INT,
    PRIMARY KEY (product_id)
);

Dynamic Pivoting Solution:

MySQL does not have a built-in PIVOT function, but a dynamic solution can be achieved using aggregate functions and a CASE statement:

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;

Example Output:

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

Static Pivoting Solution (for a limited number of columns):

For a static pivoting solution with a limited number of columns, the following 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;

Output:

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

The above is the detailed content of How to Dynamically Pivot Data in MySQL Using Aggregate Functions and CASE Statements?. For more information, please follow other related articles on the PHP Chinese website!