Multi-level hierarchy data display in SQL Server Management Studio

Question

I want to fetch a multi-level hierarchy in a sequential manner. I have a table BOMHierarchy in which I have this sample data:

Parent	Child
Parent1	Child1
Parent1	child2
Child1	Child3
Child1	Child4
Child3	Child5
Child3	Child6

I want to show the above data like below in proper hierarchical manner:

Parent	Child
Parent1	Child1
Child1	Child3
Child3	Child5
Child3	Child6
Child1	Child4
Parent1	Child2

I am stuck at fetching this sequential data according to the hierarchy. Can anyone please provide a solution?

I have tried using a CTE and while loop but I'm not getting the required result.

Answer 1

Looks like a classic problem of how to recursively scan a tree. In SQL is simple, what you just need is to create the right ORDER BY. Try something like this

DECLARE @BOM table (Parent varchar(20), Child varchar(20))

INSERT INTO @BOM(Parent, Child)
VALUES ('Parent1', 'Child1'),
       ('Parent1', 'Child2'),
       ('Child1', 'Child3'), ('Child1', 'Child4'),
       ('Child3', 'Child5'), ('Child3', 'Child6')

-- find all possible combinations recursively 
;WITH cte AS 
( 
    SELECT 
        Parent, Child,  
        CONVERT(VARCHAR(MAX),Parent + '|' + Child) Chain
    FROM 
        @BOM root
    WHERE 
        NOT EXISTS (SELECT 1 
                    FROM @BOM NotRoot
                    WHERE root.Parent = NotRoot.Child)
    UNION ALL 
    SELECT 
        BOM.Parent, BOM.Child, cte.Chain + '|' + CONVERT(VARCHAR(MAX), BOM.Child) Chain
    FROM 
        cte 
    INNER JOIN 
        @BOM BOM ON cte.Child = BOM.Parent
)
SELECT 
    Parent, Child
FROM
    cte
ORDER BY 
    Chain