Solved: SQL BOM Indentation data manipulation

I need help modifying data from a SQL table. This data contains the Bills of Material with indentation based off the "Level". However, I need to output a format that I can use in another program.

This is what the raw data looks like.

This is what I am trying to get at or expected result.

Is the above even doable based off the Level to determine which is a Parent part number and which is a sub component underneath it? Example Part C111 is top level assembly part that have sub component D111, D112, and D113 that makes that part. Then if you look at another top Sub assembly part F111, it have component G111, G112, and G113. However, G113 is a top sub assembly underneath F111 which have sub component H111 and H112.

Here's the data.

DECLARE @BILLOFMATERIAL TABLE
(
LVL VARCHAR(10),
PART VARCHAR(30),
QTY DECIMAL(12, 2),
FP VARCHAR(2)
)

INSERT INTO @BILLOFMATERIAL
SELECT '1' LVL, 'A111' PART, 1 QTY, 'F' FP UNION ALL 
SELECT '2' LVL, 'B111' PART, 4 QTY, 'P' FP UNION ALL 
SELECT '3' LVL, 'C111' PART, 2 QTY, 'F' FP UNION ALL 
SELECT '3.1' LVL, 'D111' PART, 2 QTY, 'F' FP UNION ALL 
SELECT '3.2' LVL, 'D112' PART, 1 QTY, 'P' FP UNION ALL 
SELECT '3.3' LVL, 'D113' PART, 2 QTY, 'P' FP UNION ALL 
SELECT '4' LVL, 'E111' PART, 2 QTY, 'P' FP UNION ALL 
SELECT '5' LVL, 'F111' PART, 1 QTY, 'F' FP UNION ALL 
SELECT '5.1' LVL, 'G111' PART, 1 QTY, 'P' FP UNION ALL 
SELECT '5.2' LVL, 'G112' PART, 1 QTY, 'P' FP UNION ALL 
SELECT '5.3' LVL, 'G113' PART, 2 QTY, 'F' FP UNION ALL 
SELECT '5.3.1' LVL, 'H111' PART, 1 QTY, 'P' FP UNION ALL 
SELECT '5.3.2' LVL, 'H112' PART, 2 QTY, 'P' FP 

SELECT   *
FROM   @BILLOFMATERIAL

Open in new window