How to Dynamically Convert Rows to Columns in MySQL Based on Multiple Columns?

Dynamic Conversion of Rows to Columns Based on Multiple Columns in MySQL

In a previous question, a MySQL query was used to dynamically convert rows to columns for a single column. Now, we aim to achieve the same result for two columns: "data" and "price."

Problem:

Convert the data in the following table into columns named "order1," "order2," "order3," "item1," "item2," "item3," and "item4" based on the "order" and "item" columns.

id	order	data	item	Price
1	1	P	1	50
1	1	P	2	60
1	1	P	3	70
1	2	Q	1	50
1	2	Q	2	60
1	2	Q	3	70
2	1	P	1	50
2	1	P	2	60
2	1	P	4	80
2	3	S	1	50
2	3	S	2	60
2	3	S	4	80

Desired Result:

id	order1	order2	order3	item1	item2	item3	item4
1	P	Q		50	60	70
2	P		S	50	60		80

Solution:

While a hard-coded query could be used if the number of "order" and "item" values is known, a more dynamic solution is required in the general case.

Unpivoting the Data:

MySQL lacks an unpivot function, but a UNION ALL can be used to convert the multiple data pairs into rows:

select id, concat('order', `order`) col,  data value
from tableA
union all
select id, concat('item', item) col, price value
from tableA;

Converting Values Back into Columns:

The unpivoted data can then be converted back into columns using an aggregate function with CASE:

select id, 
  max(case when col = 'order1' then value end) order1,
  max(case when col = 'order2' then value end) order2,
  max(case when col = 'order3' then value end) order3,
  max(case when col = 'item1' then value end) item1,
  max(case when col = 'item2' then value end) item2,
  max(case when col = 'item3' then value end) item3
from
(
  select id, concat('order', `order`) col,  data value
  from tableA
  union all
  select id, concat('item', item) col, price value
  from tableA
) d
group by id;

Dynamic Prepared Statement:

Finally, the query can be converted into a dynamic prepared statement using set-based concatenation and EXECUTE:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when col = ''',
      col,
      ''' then value end) as `', 
      col, '`')
  ) INTO @sql
FROM
(
  select concat('order', `order`) col
  from tableA
  union all
  select concat('item', `item`) col
  from tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  from
                  (
                    select id, concat(''order'', `order`) col,  data value
                    from tableA
                    union all
                    select id, concat(''item'', item) col, price value
                    from tableA
                  ) d
                  group by id');

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

This approach provides a flexible and dynamic way to convert rows to columns based on multiple criteria in MySQL.

The above is the detailed content of How to Dynamically Convert Rows to Columns in MySQL Based on Multiple Columns?. For more information, please follow other related articles on the PHP Chinese website!