By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE warehouse
(`Id` int, `name` varchar(11))
;
INSERT INTO warehouse
(`Id`, `name`)
VALUES
(1, 'Warehouse 1'),
(2, 'Warehouse 2')
;
CREATE TABLE items
(`Id` int, `description` varchar(6))
;
INSERT INTO items
(`Id`, `description`)
VALUES
(1, 'Item 1'),
(2, 'Item 2'),
(3, 'Item 3')
;
CREATE TABLE itemmovement
(`itemid` int, `qtyin` int, `qtyout` int, `warehouseid` int)
;
INSERT INTO itemmovement
(`itemid`, `qtyin`, `qtyout`, `warehouseid`)
VALUES
(1, 2, NULL, 1),
(1, NULL, 1, 1),
(1, 2, NULL, 2),
(2, 1, NULL, 1),
(2, 3, NULL, 1),
(2, NULL, 1, 1),
(2, 1, NULL, 2)
;
CREATE PROCEDURE stocktake()
BEGIN
DECLARE wid INT;
DECLARE wname VARCHAR(20);
DECLARE query VARCHAR(500) DEFAULT '';
DECLARE finished INT DEFAULT 0;
DECLARE whouse_cursor CURSOR FOR SELECT Id, name FROM warehouse;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN whouse_cursor;
SET @query = 'SELECT i.Id';
get_whouse: LOOP
FETCH whouse_cursor INTO wid, wname;
IF finished = 1 THEN
LEAVE get_whouse;
END IF;
SET @query = CONCAT(@query, ', SUM(CASE WHEN m.warehouseID=', wid, ' THEN COALESCE(m.qtyin, 0) - COALESCE(m.qtyout, 0) ELSE 0 END) AS `', wname, '`');
END LOOP get_whouse;
SET @query = CONCAT(@query, ' FROM items i LEFT JOIN itemmovement m ON m.itemid = i.Id');
SET @query = CONCAT(@query, ' GROUP BY Id');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
CALL stocktake();
| Id | Warehouse 1 | Warehouse 2 |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 3 | 1 |
| 3 | 0 | 0 |