How to create dynamic columns using prepared statements in MySQL?


I have 4 tables related to each other.

Table location:

id | name
 1   Dallas
 2   Boston

Table item:

id | brand
 1   Chevrolet
 2   Nissan

Table item_in:

   id | location_id | item_id | quantity
    1             1         1         5
    2             2         2        10

Table item_out:

id | location_id | item_id | quantity
  1             1         1         2
  2             2         2         1

Using MySQL pivot, I was able to get the individual remaining quantities per item based on their location and item (item_in quantity subtracted by item_out quantity) and have the location names as columns. (see below)

Result:

brand    | Dallas | Boston
Chevrolet        3       0                                 
Nissan           0       9   

    

The following is the code that I used to achieve the result above:

SELECT 
        item.brand,
        IFNULL(item_in.Dallas, 0) - IFNULL(item_out.Dallas, 0) AS Dallas,
        IFNULL(item_in.Boston, 0) - IFNULL(item_out.Boston, 0) AS Boston
        FROM item
        LEFT JOIN (
            SELECT 
            item_in.item_id, 
            SUM(CASE WHEN item_in.location_id = 1 THEN item_in.quantity ELSE NULL END) AS `Dallas`,
            SUM(CASE WHEN item_in.location_id = 2 THEN item_in.quantity ELSE NULL END) AS `Boston`
            FROM item_in
            GROUP BY item_in.item_id
        ) item_in ON item.id = item_in.item_id
        LEFT JOIN (
            SELECT 
            item_out.item_id, 
            SUM(CASE WHEN item_out.location_id = 1 THEN item_out.quantity ELSE NULL END) AS `Dallas`,
            SUM(CASE WHEN item_out.location_id = 2 THEN item_out.quantity ELSE NULL END) AS `Boston`
            FROM item_out
            GROUP BY item_out.item_id
        ) item_out ON item.id = item_out.item_id

My question, how do I go about changing the code above that allows the location name columns to be dynamic instead of hardcoding them manually in the query since users can add new locations anytime?

Already tried googling regarding using prepared statements but I can’t seem to grasp the concept on how to do it. Would really appreciate any help.

Update:

Tried tinkering around with dynamic SQL but no luck so far. i feel I’m getting close though, if anyone can take a look at my code, i’d really appreciate the help. my code attempt below:

SET @sql = NULL, @sql1 = NULL, @sql2 = NULL;

SELECT GROUP_CONCAT( DISTINCT
          CONCAT('SUM(CASE WHEN `location_id` = ''',`location_id`, ''' THEN quantity END) AS ',`name`))
          INTO @sql1
          FROM item_in
          JOIN location on location.id = item_in.location_id;
        
SELECT GROUP_CONCAT( DISTINCT
          CONCAT('SUM(CASE WHEN `location_id` = ''',`location_id`, ''' THEN quantity END) AS ',`name`))
          INTO @sql2
          FROM item_out
          JOIN location on location.id = item_out.location_id;
          
SET @sql = CONCAT('SELECT item.brand AS Item, IFNULL(item_in.Dallas, NULL) - IFNULL(item_out.Dallas, NULL) AS Dallas, IFNULL(item_in.Boston, NULL) - IFNULL(item_out.Boston, NULL) AS Boston FROM item LEFT JOIN (SELECT item_in.item_id, ', @sql1, ' FROM item_in
                    GROUP BY item_in.item_id) AS item_in ON items.id = item_in.item_id LEFT JOIN (SELECT item_out.item_id, ', @sql2, ' FROM item_out
                    GROUP BY item_out.item_id) AS item_out ON items.id = item_out.item_id');
                    
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The only part I’m having trouble is how to not hardcode these lines and do them dynamically:

IFNULL(item_in.Dallas, NULL) - IFNULL(item_out.Dallas, NULL) AS Dallas, IFNULL(item_in.Boston, NULL) - IFNULL(item_out.Boston, NULL) AS Boston