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.

*"I have tried using CTE and while loop but not getting the required result."* What were those attempts? Why didn't they work? — Thom A, Feb 10 '23 at 16:12
As a side note, why are you still using SSMS 2012? SSMS 19 came out last month, and still supports versions as old as SQL Server 2008 (not that SQL Server 2008-2012 are actually supported versions of SQL Server). — Thom A, Feb 10 '23 at 16:12
@Larnu , it was a mistyped tag , cant find SSMS19 in the tags. lol. also answer to your first comment is I have iterated the table and tried putting one record into temp table at a time , it works only 2 level , it is not considering 3 or more level data . I am getting o/p as below Parent1-child1, child1-child3 , child3-child4 — Kantarkatax, Feb 10 '23 at 16:19
Depending on how often you need to determine the hierarchy, it might be worth persisting it with a hierarchyid datatype. I did a fairly extensive write-up on how to do that [here](https://stackoverflow.com/a/71541261/568209). — Ben Thul, Feb 10 '23 at 21:45

score 0 · Accepted Answer · edited Feb 10 '23 at 17:07

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

Exactly what I needed. Genuinely thank you for your efforts. Marked your answer as Accepted. — Kantarkatax, Feb 10 '23 at 17:13

Multi-level hierarchy data display in SQL Server Management Studio

1 Answers1