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, Aug 14 '18 at 03:02
You asked about creating dynamic columns in MySQL. That's what a dynamic pivot is. — Barmar, Aug 14 '18 at 03:03
@Barma Yes. But seems my case is a little different from that case — moses toh, Aug 14 '18 at 03: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, Aug 14 '18 at 03:13
@Barmar Okay. But seems I had explain my problem more clearly. But I will try to update it again — moses toh, Aug 14 '18 at 03:15
See all the linked questions on the right. They all show how to write dynamic SQL to do this. Try to implement it, and post your attempted code. — Barmar, Aug 14 '18 at 04:07
@Barmar Okay. I am trying harder to understand the process. This case is really difficult — moses toh, Aug 14 '18 at 08:04

score 4 · Accepted Answer · answered Aug 14 '18 at 05:03

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 '18 at 05:03

Mihai

26,325
7
66
81

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 Aug 14 '18 at 08:02
I did not find `select * from locations` in the your script. How to take the field? – moses toh Aug 14 '18 at 08:17
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 Aug 14 '18 at 08:29
@SuccessMan It`s hard for me to guess where the problem might be.Create a sqlfiddle and try to reproduce the problem with a few rows of data. http://www.sqlfiddle.com/ – Mihai Aug 14 '18 at 08:32
Okay. I will create a sqlfiddle – moses toh Aug 14 '18 at 08:45
Look at this : http://www.sqlfiddle.com/#!9/c8f62b/4. There exist error – moses toh Aug 14 '18 at 09:17
Seems it works. But I will review it again. I want to ask. Are there other solutions without many statements? Because there exist `PREPARE`, `EXECUTE` etc – moses toh Aug 14 '18 at 10:04
So I mean, is it possible to only use 1 statement for this case? – moses toh Aug 14 '18 at 10:35
There are solutions but you have to hardcode ALL the values,smth like SUM(CASE WHEN location_code = 'FACTORY' blah) and so on for each value.The dynamic pivot has the advantage you dont need to specify those values even if you add or delete new locations. – Mihai Aug 14 '18 at 10:58
Okay. I using laravel framework. I must convert mysql query to laravel eloquent. It's a bit difficult. Because there are many statements – moses toh Aug 14 '18 at 11:03
put this line `SELECT @sql;` before PREPARE... it will output the resulted query.You can copy that. – Mihai Aug 14 '18 at 11:17
Where line `SELECT @sql`? I don't find it – moses toh Aug 14 '18 at 11:25
1

I received your answer because actually your answer had answered my question. For conversion problems to laravel eloquent. That is another problem. Thanks – moses toh Aug 16 '18 at 11:00
I want to ask again. Can you help me? – moses toh Aug 24 '18 at 09:05
So I want to add conditions before sum quantity. I have another field called `type`. if(type = "sales") then sum quantity. Like that. How can I add it? – moses toh Aug 24 '18 at 09:07
1

`SELECT item_number,SUM(CASE WHEN type='sales' THEN wahatever END)as smth,SUM(quantity)` – Mihai Aug 24 '18 at 09:33

score 0 · Answer 2 · answered Aug 14 '18 at 03:34

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

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

2 Answers2

Linked