Write SQL query to put hirarchy-based data into Level-based table format

Transact-SQL
, ,
1

Hi all, Can someone please help me putting the sql query from one table into the format shown in the attached picture?
I have a merhcant hierarchy data stored into one table and I would like to transform that into Level-based table format.

Thank you.

Capture
Capture770×848 40.8 KB

2

As it's a one time transformation I would like to keep it simple:

INSERT INTO NewTable(Level1,Level2,Level3, Level4, Description)
SELECT DISTINCT
FOB# AS Level1,
'' AS Level2,
'' AS Level3,
'' AS Level4,
FOB AS Description
FROM YourOldTable
UNION ALL
SELECT DISTINCT
FOB# AS Level1,
DIV# AS Level2,
'' AS Level3,
'' AS Level4,
DIVISION AS Description
FROM YourOldTable
UNION ALL
SELECT DISTINCT
....

3

Thank you for quick help.
QQ: Are we missing queries after (last two lines)?
UNION ALL
SELECT DISTINCT

4

nvrmnd sir. All good now. Appreciate it!