How to Perform Dynamic Data Pivoting in MySQL?

Dynamic pivoting of data in MySQL

Data pivoting is a common operation in data transformation, which involves reshaping the data from a vertical layout to a horizontal layout. In SQL, this can be achieved using different techniques such as UNPIVOT or PIVOT functions. However, MySQL does not have a native PIVOT function, making it necessary to explore alternative methods.

One approach to dynamic pivoting in MySQL is to dynamically construct a query using aggregate functions and CASE statements. This method involves dynamically building the SQL statement based on the dimensions and values required in the output.

The following example demonstrates how to implement dynamic pivoting using 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;

In this query, we dynamically generate a SELECT statement based on the distinct part types, which are the dimensions of the pivot. We use the CASE statement to assign the part_id to the appropriate pivot column based on the part_type. The generated SQL statement is then executed using prepared statements for better performance.

Another option is to use a static version of the query if you know the limited number of columns needed in the output:

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

By leveraging these techniques, you can implement dynamic pivoting in MySQL to reshape your data efficiently.

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