How to create dynamic columns using prepared statements in MySQL?

Question

I have 4 tables related to each other.

CREATE TABLE `location` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `location` (`id`, `name`) VALUES
(1, 'Dallas'),
(2, 'Boston'),
(3, 'Houston');

CREATE TABLE `item` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `brand` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item` (`id`, `brand`) VALUES
(1, 'Nissan Almera M/T 2009-2015'),
(2, 'Toyota Corolla A/T 2005-2012'),
(3, 'Nissan Terra A/T 2010-2017'),
(4, 'Suzuki Esteem M/T 1980-1990'),
(5, 'Toyota Fortuner A/T 2014-2020');

CREATE TABLE `item_in` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `location_id` bigint(20) UNSIGNED NOT NULL,
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item_in` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 1, 1000),
(2, 1, 2, 500),
(3, 2, 2, 200),
(4, 2, 2, 300),
(5, 3, 3, 300),
(6, 1, 3, 800),
(7, 3, 5, 300),
(8, 3, 4, 400);

CREATE TABLE `item_out` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `location_id` bigint(20) UNSIGNED NOT NULL,
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item_out` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 2, 20),
(2, 1, 1, 25),
(3, 2, 2, 25),
(4, 3, 3, 25),
(5, 3, 5, 10),
(6, 3, 4, 15),
(7, 1, 1, 200),
(8, 2, 2, 50);

Using dynamic SQL, 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 code 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, 0) - IFNULL(item_out.Dallas, 0) AS Dallas, IFNULL(item_in.Boston, 0) - IFNULL(item_out.Boston, 0) AS Boston, IFNULL(item_in.Houston, 0) - IFNULL(item_out.Houston, 0) AS Houston FROM item LEFT JOIN (SELECT item_in.item_id, ', @sql1, ' FROM item_in
                    GROUP BY item_in.item_id) AS item_in ON item.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 item.id = item_out.item_id');
                    
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Result:

Item                           | Dallas | Boston | Houston
Nissan Almera M/T 2009-2015          775        0         0                           
Toyota Corolla A/T 2005-2012         480      425         0
Nissan Terra A/T 2010-2017           800        0       275
Suzuki Esteem M/T 1980-1990            0        0       385
Toyota Fortuner A/T 2014-2020          0        0       290

My question, how do I go about changing the code so that the location name columns is displayed dynamically instead of hardcoding them manually in the query since users can add new locations anytime? if anyone can take a look at my code, i'd really appreciate the help. The only part I'm having trouble is how to not hardcode these lines and do them dynamically:

IFNULL(item_in.Dallas, 0) - IFNULL(item_out.Dallas, 0) AS Dallas, IFNULL(item_in.Boston, 0) - IFNULL(item_out.Boston, 0) AS Boston, IFNULL(item_in.Houston, 0) - IFNULL(item_out.Houston, 0) AS Houston

Answer 1

By way of example, consider the following, which uses PHP and the mysqli_ API. (I've used procedural code, but dbo would be more efficient)...

<?php

/*
DROP TABLE IF EXISTS location;

CREATE TABLE `location` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO `location` (`id`, `name`) VALUES
(1, 'Dallas'),
(2, 'Boston'),
(3, 'Houston');

DROP TABLE IF EXISTS item;
CREATE TABLE `item` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `brand` varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO `item` (`id`, `brand`) VALUES
(1, 'Nissan Almera M/T 2009-2015'),
(2, 'Toyota Corolla A/T 2005-2012'),
(3, 'Nissan Terra A/T 2010-2017'),
(4, 'Suzuki Esteem M/T 1980-1990'),
(5, 'Toyota Fortuner A/T 2014-2020');

DROP TABLE IF EXISTS item_in;
CREATE TABLE `item_in` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `location_id` bigint(20) UNSIGNED NOT NULL,
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO `item_in` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 1, 1000),
(2, 1, 2, 500),
(3, 2, 2, 200),
(4, 2, 2, 300),
(5, 3, 3, 300),
(6, 1, 3, 800),
(7, 3, 5, 300),
(8, 3, 4, 400);

DROP TABLE IF EXISTS item_out;
CREATE TABLE `item_out` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `location_id` bigint(20) UNSIGNED NOT NULL,
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO `item_out` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 2, 20),
(2, 1, 1, 25),
(3, 2, 2, 25),
(4, 3, 3, 25),
(5, 3, 5, 10),
(6, 3, 4, 15),
(7, 1, 1, 200),
(8, 2, 2, 50);

*/

require('path/to/connect.ion');

$query = "
SELECT l.name city
     , i.brand item
     , SUM(x.quantity) total
  FROM
     ( SELECT location_id,item_id,'in' type, quantity FROM item_in
        UNION ALL
       SELECT location_id,item_id,'out',quantity*-1 FROM item_out
     ) x
  JOIN location l
    ON l.id = x.location_id
  JOIN item i
    ON i.id = x.item_id
 GROUP
    BY item
     , city
 ORDER
    BY city
     , item
";

$result = mysqli_query($db,$query);

$array = array();

while($row = mysqli_fetch_assoc($result)){
$array[] = $row;
}

foreach($array as $v){
$new_array[$v['city']][$v['item']] = $v['total'];
}


print_r($new_array);
?>

Outputs:

Array
(
    [Boston] => Array
        (
            [Toyota Corolla A/T 2005-2012] => 425
        )

    [Dallas] => Array
        (
            [Nissan Almera M/T 2009-2015] => 775
            [Nissan Terra A/T 2010-2017] => 800
            [Toyota Corolla A/T 2005-2012] => 480
        )

    [Houston] => Array
        (
            [Nissan Terra A/T 2010-2017] => 275
            [Suzuki Esteem M/T 1980-1990] => 385
            [Toyota Fortuner A/T 2014-2020] => 290
        )

)

Or you can swap city and item around in $new_array[$v['city']][$v['item']] = $v['total'];, to get:

Array
(
    [Toyota Corolla A/T 2005-2012] => Array
        (
            [Boston] => 425
            [Dallas] => 480
        )

    [Nissan Almera M/T 2009-2015] => Array
        (
            [Dallas] => 775
        )

    [Nissan Terra A/T 2010-2017] => Array
        (
            [Dallas] => 800
            [Houston] => 275
        )

    [Suzuki Esteem M/T 1980-1990] => Array
        (
            [Houston] => 385
        )

    [Toyota Fortuner A/T 2014-2020] => Array
        (
            [Houston] => 290
        )

)