group lots, Sql Server

Language: Layout:

if exists (select * from INFORMATION_SCHEMA.TABLES 
           where TABLE_NAME = 'containers')
    drop table containers;

if exists (select * from INFORMATION_SCHEMA.TABLES 
           where TABLE_NAME = 'orders_items')
    drop table orders_items;

create table containers (
    id int primary key, 
    name text, 
    product_id int, 
    lot int, 
    qty int, 
    parent_id int);

create table orders_items (
    id int,
    position int,
    container_id int);
    
insert into containers values
(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),
(5,  'Bag 20',  1, null, 20,  7),
(6,  'Bag 20',  1, null, 20,  7),
(7,  'Box 40',  1, null, 40,  16),
(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),
(12, 'Bag 20',  1, null, 20,  14),
(13, 'Bag 20',  1, null, 20,  14),
(14, 'Box 40',  1, null, 40,  16),
(15, 'Box 40',  1, 600,  40,  16),
(16, 'Pallet',  1, null, 120, null),
(17, 'Bag 10',  2, 700,  10,  19),
(18, 'Bag 10',  2, 701,  10,  19),
(19, 'Bag 20',  2, null, 20,  23),
(20, 'Bag 10',  2, 703,  10,  22),
(21, 'Bag 10',  2, 701,  10,  22),
(22, 'Bag 20',  2, null, 20,  23),
(23, 'Box 40',  2, null, 40,  null);

insert into orders_items values
(1, 1, 16),
(1, 2, 23);

select * from containers;
select * from orders_items;

View schema
Execution time: 0,02 sec, rows selected: 25, rows affected: 25, absolute service time: 0,16 sec