How to create select dynamic fields from a table in mysql?

Question

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

@Barmar Why is this question marked as duplicate? It really doesn't help me — moses toh
– moses toh, Commented Aug 14, 2018 at 3:02
You asked about creating dynamic columns in MySQL. That's what a dynamic pivot is. — Barmar
– Barmar, Commented Aug 14, 2018 at 3:03
@Barma Yes. But seems my case is a little different from that case — moses toh
– moses toh, Commented Aug 14, 2018 at 3:07
I've reopened. You need to explain your problem more clearly. Show the result you're trying to get. And make an attempt to code it yourself, then we'll help you fix it; we don't write the code for you from scratch. — Barmar
– Barmar, Commented Aug 14, 2018 at 3:13
@Barmar Okay. But seems I had explain my problem more clearly. But I will try to update it again — moses toh
– moses toh, Commented Aug 14, 2018 at 3:15

Mihai · Accepted Answer · 2018-08-14 05:03:42Z

4

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;

answered Aug 14, 2018 at 5:03

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

moses toh Over a year ago

Thanks. But I'm really confused with the script. Can you explain in more detail? Or provide solutions to other answers that are easier. Now I am trying harder to understand the process

moses toh Over a year ago

I did not find select * from locations in the your script. How to take the field?

moses toh Over a year ago

I had try your script, there is no error. Column total_quantity, the results are correct. But column store and colomn factory, the result is incorrect

moses toh Over a year ago

I received your answer because actually your answer had answered my question. For conversion problems to laravel eloquent. That is another problem. Thanks

Mihai Over a year ago

SELECT item_number,SUM(CASE WHEN type='sales' THEN wahatever END)as smth,SUM(quantity)

|

JERRY · Accepted Answer · 2018-08-14 03:34:44Z

0

If you are using stored procedure then your query will become easy to use dynamically. Dynamic column name you can manage by integer number or name of columns.
store your existing query in the variable and use If condition to concat your dynamic query in variable. at last execute the statement by defined variable.

you can take reference from below link for execute the dynamic code. mysql dynamic query in stored procedure

answered Aug 14, 2018 at 3:34

JERRY

1,1581 gold badge8 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to create select dynamic fields from a table in mysql?

2 Answers 2

16 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related