mysql 动态选择字段,如何从mysql中的表创建选择动态字段？

I have query join in select statement like this :

select a.item_number, total_quantity, store, factory

from (

select item_number, sum(quantity) as "total_quantity"

from `item_details`

group by item_number

) `a`

left join (

select item_number, sum(quantity) as 'store'

from `item_details` where location_code = 'STORE'

group by item_number

) `b` on `a`.`item_number` = `b`.`item_number`

left join (

select item_number, sum(quantity) as 'factory'

from `item_details`

where location_code = 'FACTORY'

group by item_number

) `c` on `a`.`item_number` = `c`.`item_number`

order by `item_number` asc

From the query above, it appears if I use table item_details with fields id, item_no, quantity and location_code

If the query executed, the result like this :

The results are correct. But here I want to create field store and factory to be dynamic. So it's taken from table locations. Because the data location is dynamic. It can be added and removed

So I have table locations with field id and description like this :

field location_code in the item_details table is foreign key to field id in locations table

So how to create select dynamic fields from location table?

Note :

I use "query join in select statement" because I didn't have table locations before. Now I use table locations. Because the data in the location table is dynamic. It can be added and removed. So I want to display it like table 1 above with table location. Seems it need to join the table. But i'm still confused to do it

解决方案

This is not tested ,create a fiddle if you find errors.

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ifnull(SUM(case when location_code = ''',

location_code ,

''' then quantity end),0) AS `',

location_code , '`'

)

) INTO @sql

FROM

item_details;

SET @sql = CONCAT('SELECT item_number,SUM(quantity) as "total_quantity", ', @sql, '

FROM item_details

GROUP BY item_number');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;