MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE product (Product VARCHAR(16), 
                      Component VARCHAR(16), 
                      Quantity INT, 
                      Line_number INT);
INSERT INTO product VALUES 
('Product1','Part1'    ,1        ,1),
('Product1','Product2' ,2        ,2),    
('Product1','Part2'    ,1        ,3),
('Product2','Product3' ,4        ,1),
('Product3','Part3'    ,2        ,2),
('Product2','Part4'    ,4        ,2),
('Product9','Part8'    ,5        ,1);
WITH RECURSIVE
cte (sProduct, sComponent, sQuantity, Product, Component, Quantity, HaveChilds) 
AS ( SELECT Product, 
            Component, 
            Quantity, 
            Product, 
            Component, 
            Quantity,
            EXISTS ( SELECT 1
                     FROM product p2
                     WHERE p1.Component = p2.Product )
     FROM product p1
     WHERE NOT EXISTS ( SELECT 1
                        FROM product p3
                        WHERE p1.Product = p3.Component )
  UNION ALL
     SELECT cte.sProduct, 
            cte.sComponent, 
            cte.sQuantity, 
            p1.Product, 
            p1.Component, 
            cte.Quantity * p1.Quantity,
            EXISTS ( SELECT 1
                     FROM product p2
                     WHERE p1.Component = p2.Product )
     FROM cte, product p1
     WHERE cte.Component = p1.Product
	   AND cte.HaveChilds = 1 
)
SELECT sProduct Product, Component, Quantity, ROW_NUMBER() OVER (PARTITION BY sProduct ORDER BY Component) Line_Number
FROM cte
WHERE HaveChilds = 0
ORDER BY 1, 2;


Product
Component
Quantity
Line_Number


Product1
Part1
1
1

Product1
Part2
1
2

Product1
Part3
16
3

Product1
Part4
8
4

Product9
Part8
5
1

Product	Component	Quantity	Line_Number
Product1	Part1	1	1
Product1	Part2	1	2
Product1	Part3	16	3
Product1	Part4	8	4
Product9	Part8	5	1