Recursive CTE showing all levels of ragged-hierarchy BOMs

Question

I’m trying to create a recursive query in T-SQL This query should return all active BOMs and the hierarchy of parts through all levels of each BOM.

I cannot use temporary tables, as I am using Azure Synapse serverless.

The desired output is this: the first table traces the part through the subcomponents to the parent BOM.

Child Part No.	Child Part Name	Parent BOM ID	Parent Part No.	Lvl
15-5H10001.312	15-5PH AMS5659 TYPE 1 AMS-H-6875, 150,000 PSI MIN ULT (REF H1000)	BOM0013535	9R3212	2
15-5H10001.312	15-5PH AMS5659 TYPE 1 AMS-H-6875, 150,000 PSI MIN ULT (REF H1000)	BOM0013536	9R3213-2	1

The second table takes the part and traces it back through the parts / subassemblies all the way down to raw materials:

Parent Part No.	Child Part No.	Child Part Name	Child BOM ID	Lvl
293W3001-1	BUS70M215AT99	PLUS SEAL BACKUP RING	BOM-015307	1
293W3001-1	S30855-0215H-99	GROOVED PLUS SEAL II	BOM0000712	2
293W3001-1	293W3002-2	BARREL, ACTUATOR 3.375 X 12.000 SLUGGED MATERIAL	BOM0003067	3
293W3001-1	2C5041-4	POPPET ANY VENDOR CHANGES, NOTIFY CONTRACTS	BOM0000620	4
293W3001-1	O130-17-4H1075.1875	HEAT TREAT	BOM0000924	5
293W3001-1	304A.2500	CENTERLESS GROUND AMS5639, QQ-S-763	BOM0007194	6

Normal recursive query strategies don’t seem to work, in part because I don’t have a defined starting point, and in part because the joins are somewhat complicated (it’s not a simple hierarchy of ID-Parent -> ID-Child -> ID-Parent2… hard to explain; hopefully, my code makes it clearer).

I’ve currently something that works, but it’s clunky and only works down to 7-level BOMs; I want something that just goes to the end of the hierarchy, no matter how deep it is (I’ll only give you the first two select statements, to minimize clutter:

SELECT DISTINCT  H.BOMID AS ParentBOMID  
    , H.BOMName AS ParentPartID  
    , I1.ItemName AS ParentPartName   
    , H.BOMID AS ChildBOMID  
    , L.ItemNumber AS ChildPartNumber  
    , I2.ItemName AS ChildPartName  
    , [Level] = 1 
FROM ARBBOMItemSpecificBillOfMaterialsHeaderEntity AS H 
LEFT OUTER JOIN bi.DIM_Item AS I1  
    ON H.BOMName = I1.ItemID 
LEFT OUTER JOIN ARBBOMBillOfMaterialsLineEntity AS L  
    ON H.BOMID = L.BOMID 
LEFT OUTER JOIN bi.DIM_Item AS I2  
    ON L.ItemNumber = I2.ItemID  

UNION  

SELECT  
    H.BOMID AS ParentBOMID  
    , H.BOMName AS ParentPartID  
    , I1.ItemName AS ParentPartName   

    , H2.BOMID AS ChildBOMID  
    , L2.ItemNumber AS ChildPartNumber  
    , I3.ItemName AS ChildPartName  
    , [Level] = 2 
FROM ARBBOMItemSpecificBillOfMaterialsHeaderEntity AS H 
LEFT OUTER JOIN bi.DIM_Item AS I1  
    ON H.BOMName = I1.ItemID 
LEFT OUTER JOIN ARBBOMBillOfMaterialsLineEntity AS L  
    ON H.BOMID = L.BOMID 
LEFT OUTER JOIN bi.DIM_Item AS I2  
    ON L.ItemNumber = I2.ItemID   
LEFT OUTER JOIN ARBBOMBillOfMaterialsHeaderEntity AS H2  
    ON L.ItemNumber = H2.BOMName 
LEFT OUTER JOIN ARBBOMBillOfMaterialsLineEntity AS L2  
    ON H2.BOMID = L2.BOMID 
LEFT OUTER JOIN bi.DIM_Item AS I3  
    ON L2.ItemNumber = I3.ItemID  

…. and so forth, all the way down to L6 and I7