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

Question

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.

Answer 1

Since you only have 4 levels this would be simplest.

;with levelcte AS (
    SELECT DISTINCT 
        [FOB #] as [Level1],
        NULL as [Level2],
        NULL as [Level3],
        NULL as [Level4],
        [FOB] as [Description]
    FROM tablename
    UNION ALL
    SELECT DISTINCT
        [FOB #] as [Level1],
        [DIV #] as [Level2],
        NULL as [Level3],
        NULL as [Level4],
        [DIVISION] as [Description]
    FROM tablename
    UNION ALL
    SELECT DISTINCT
        [FOB #] as [Level1],
        [DIV #] as [Level2],
        [DEPT#] as [Level3],
        NULL as [Level4],
        [DEPARTMENT] as [Description]
    FROM tablename
    UNION ALL
    SELECT DISTINCT
        [FOB #] as [Level1],
        [DIV #] as [Level2],
        [DEPT#] as [Level3],
        [CLS #] as [Level4],
        [CLASS] as [Description]
    FROM tablename
)
SELECT *
levelcte