Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE dbo.Containers > ( > ContainerID int NOT NULL > CONSTRAINT PK_containers > PRIMARY KEY CLUSTERED > , ContainerName text NOT NULL > , ProductID int NOT NULL > , Lot int NULL > , Quantity int NOT NULL > , ParentContainerID int NULL > CONSTRAINT FK_Containers_ContainerID > FOREIGN KEY > REFERENCES dbo.Containers (ContainerID) > ON UPDATE NO ACTION > ON DELETE NO ACTION > ); > > CREATE TABLE dbo.OrdersItems > ( > OrderID INT NOT NULL > , Position int NOT NULL > , ContainerID int NULL > CONSTRAINT FK_OrdersItems_ContainerID > FOREIGN KEY > REFERENCES dbo.Containers (ContainerID) > ON UPDATE NO ACTION > ON DELETE NO ACTION > , CONSTRAINT PK_OrdersItems > PRIMARY KEY CLUSTERED (OrderID, Position) > ); > > INSERT INTO dbo.Containers (ContainerID, ContainerName, ProductID > , Lot, Quantity, ParentContainerID) > VALUES > (23, 'Box 40', 2, null, 40, null) > , (16, 'Pallet', 1, null, 120, null) > , (12, 'Bag 20', 1, null, 20, 14) > , (13, 'Bag 20', 1, null, 20, 14) > , (14, 'Box 40', 1, null, 40, 16) > , (19, 'Bag 20', 2, null, 20, 23) > , (22, 'Bag 20', 2, null, 20, 23) > , (5, 'Bag 20', 1, null, 20, 7) > , (6, 'Bag 20', 1, null, 20, 7) > , (7, 'Box 40', 1, null, 40, 16) > , (1, 'Bag 10', 1, 500, 10, 5) > , (2, 'Bag 10', 1, 501, 10, 5) > , (3, 'Bag 10', 1, 502, 10, 6) > , (4, 'Bag 10', 1, 500, 10, 6) > , (8, 'Bag 10', 1, 600, 10, 12) > , (9, 'Bag 10', 1, 601, 10, 12) > , (10, 'Bag 10', 1, 502, 10, 13) > , (11, 'Bag 10', 1, 501, 10, 13) > , (15, 'Box 40', 1, 600, 40, 16) > , (17, 'Bag 10', 2, 700, 10, 19) > , (18, 'Bag 10', 2, 701, 10, 19) > , (20, 'Bag 10', 2, 703, 10, 22) > , (21, 'Bag 10', 2, 701, 10, 22); > > INSERT INTO dbo.OrdersItems (OrderID, Position, ContainerID) > VALUES (1, 1, 16) > , (1, 2, 23); > GO > > <pre> 25 rows affected > </pre> <!-- --> > ;WITH RecursiveCTE AS > ( > SELECT c1.ContainerID > , c1.ContainerName > , c1.Lot > , c1.ParentContainerID > , c1.ProductID > , c1.Quantity > , Level = 1 > FROM dbo.Containers c1 > WHERE c1.ParentContainerID IS NULL > UNION ALL > SELECT c2.ContainerID > , c2.ContainerName > , c2.Lot > , c2.ParentContainerID > , c2.ProductID > , c2.Quantity > , Level = RecursiveCTE.Level + 1 > FROM dbo.Containers c2 > INNER JOIN RecursiveCTE ON RecursiveCTE.ContainerID = c2.ParentContainerID > ) > SELECT r.ProductID > , r.Lot > , QuantityTotal = SUM(r.Quantity) > FROM RecursiveCTE r > WHERE r.Lot IS NOT NULL > AND r.Level > 1 > GROUP BY r.ProductID > , r.Lot; > GO > > <pre> > ProductID | Lot | QuantityTotal > --------: | --: | ------------: > 1 | 500 | 20 > 1 | 501 | 20 > 1 | 502 | 20 > 1 | 600 | 50 > 1 | 601 | 10 > 2 | 700 | 10 > 2 | 701 | 20 > 2 | 703 | 10 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ddc0f8ad9dafdcc1ecf73d3a4e696963)*
back to fiddle