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'),
(3, 'Warehouse 3'),
(4, 'Warehouse 4'),
(5, 'Warehouse 5')
;
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),
(2, 1, NULL, 4),
(2, 3, NULL, 5),
(1, 2, NULL, 5),
(1, NULL, 1, 4),
(1, 2, NULL, 3),
(2, NULL, 1, 1),
(2, 1, NULL, 2),
(1, 2, NULL, 4),
(3, 3, NULL, 3),
(3, 3, NULL, 5),
(3, NULL, 1, 3)
;
CREATE PROCEDURE stocktake()
BEGIN
DECLARE wid INT;
DECLARE wname VARCHAR(20);
DECLARE query TEXT 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 | Warehouse 3 | Warehouse 4 | Warehouse 5 |
|---|---|---|---|---|---|
| 1 | 1 | 2 | 2 | 1 | 2 |
| 2 | 2 | 2 | 0 | 1 | 3 |
| 3 | 0 | 0 | 2 | 0 | 3 |