MariaDB 10.6

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

select version();


version()


10.6.9-MariaDB


CREATE TABLE BOM
    (`ID` int, `BOMLevel` int, `BOMType` varchar(63), `Name` varchar(63))
;
    
INSERT INTO BOM
    (`ID`, `BOMLevel`, `BOMType`, `Name`)
VALUES
    (1,  1, 'EndProduct', 'PName1'),
    (2,  1, 'EndProduct', 'PName2'),
    (3,  2, 'Assemblies', 'AsseName1'),
    (4,  2, 'Assemblies', 'AsseName2'),
    (5,  2, 'Assemblies', 'AsseName3'),
    (6,  3, 'SubAssemblies', 'SubAsseName1'),
    (7,  3, 'SubAssemblies', 'SubAsseName2'),
    (8,  4, 'RawMaterial', 'RawMName1'),
    (9,  4, 'RawMaterial', 'RawNName1')
;

CREATE TABLE BOM_PARENT (
  ID int,
  ParentID int,
  PRIMARY KEY (ID, ParentID),
  KEY (ParentID, ID)
);

INSERT INTO BOM_PARENT (ID, ParentID) VALUES
    (3, 1),
    (4, 1),
    (5, 2),
    (6, 3),
    (7, 4),
    (8, 6),
    (8, 7),
    (9, 6),
    (9, 7);


SELECT BOM.ID, 
       GROUP_CONCAT(BOM_PARENT.ParentID) Parents,
       BOM.BOMLevel, BOM.BOMType, BOM.Name
  FROM BOM
  LEFT JOIN BOM_PARENT ON BOM.ID = BOM_PARENT.ID
 GROUP BY  BOM.ID, BOM.BOMLevel, BOM.BOMType, BOM.Name


ID
Parents
BOMLevel
BOMType
Name


1
null
1
EndProduct
PName1

2
null
1
EndProduct
PName2

3
1
2
Assemblies
AsseName1

4
1
2
Assemblies
AsseName2

5
2
2
Assemblies
AsseName3

6
3
3
SubAssemblies
SubAsseName1

7
4
3
SubAssemblies
SubAsseName2

8
6,7
4
RawMaterial
RawMName1

9
6,7
4
RawMaterial
RawNName1

ID	Parents	BOMLevel	BOMType	Name
1	null	1	EndProduct	PName1
2	null	1	EndProduct	PName2
3	1	2	Assemblies	AsseName1
4	1	2	Assemblies	AsseName2
5	2	2	Assemblies	AsseName3
6	3	3	SubAssemblies	SubAsseName1
7	4	3	SubAssemblies	SubAsseName2
8	6,7	4	RawMaterial	RawMName1
9	6,7	4	RawMaterial	RawNName1