title: How to create select dynamic fields from a table in mysql?
body: <p>I have query join in select statement like this : </p>
  
  <pre><code>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
  </code></pre>
  
  <p>From the query above, it appears if I use table <code>item_details</code> with fields id, item_no, quantity and location_code</p>
  
  <p>If the query executed, the result like this :</p>
  
  <p><a href="https://i.stack.imgur.com/ru2VA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ru2VA.png" alt="enter image description here"></a></p>
  
  <p>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</p>
  
  <p>So I have table locations with field id and description like this :</p>
  
  <p><a href="https://i.stack.imgur.com/5nW0l.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5nW0l.png" alt="enter image description here"></a></p>
  
  <p>field location_code in the item_details table is foreign key to field id in locations table</p>
  
  <p>So how to create select dynamic fields from location table?</p>
  
  <p>Note : </p>
  
  <p>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</p>