CODEWITHSUNDEEP
sqlsql-serverrecursive-query
M B
M B

Reputation: 2326

Query to List all hierarchical children with parent, grandparent etc. from entire table

I have a basic sql table with an ID and an optional parentID from the same table. This has multiple levels. I need to get a list of all IDs with each of its parents all the way to the top.

ID  | Name  | ParentID
_______________________
1     John    null
2     Doe     1
3     Mike    1
4     Neil    3
5     Sylvia  4
6     Mary    2

I need to get a list like this:

ID  | Parent | DirectParent
_______________________
2     1       1
3     1       1
4     3       1
4     1       0
5     4       1
5     3       0
5     1       0

How can I query the above table to get this data?

Upvotes: 0

Views: 1096

Answers (1)

Vidmantas Blazevicius
Vidmantas Blazevicius

Reputation: 4802

A recursive CTE will do the job here. The trickiest part is to get the direct parent bit column, hopefully I understood the logic correctly from the expected result. And as a bonus I added an actual reporting hierarchy column for all possible paths. (Recursive depth is up to 10 times here)

    CREATE TABLE #MyTable
    (
    ID int primary key,
    Name nvarchar (max),
    ParentID int
    )

    INSERT INTO #MyTable
    Values (1, 'John', null),
    (2, 'Doe', 1),
    (3, 'Mike', 1),
    (4, 'Neil', 3),
    (5, 'Sylvia', 4),
    (6, 'Mary', 2)

    WITH FindRoot AS
    (
        SELECT ID, ParentId, ParentId as Parent, CAST(Name AS NVARCHAR(MAX)) Path, 0 Distance
        FROM #MyTable

        UNION ALL

        SELECT P.ID, p.ParentId, c.Parent, C.Path + N' > ' + CAST(Name AS NVARCHAR(MAX)), C.Distance + 1
        FROM #MyTable P
        JOIN FindRoot C
        ON C.ID  = P.ParentId AND P.ParentId <> P.ID and C.ParentId <> c.ID 
        WHERE C.DISTANCE < 10
     )
    SELECT R.ID, R.Parent,
    CASE WHEN R1.Parent IS NULL THEN 1
    ELSE 0
    END AS DirectParent,  R.Path as ReportingHierarchy
    FROM FindRoot R
    LEFT JOIN FindRoot R1 on R1.ID = R.ID and R1.Parent =
    (SELECT Top 1 Parent From FindRoot
    WHERE ID = R.ID and Parent > R.Parent 
    Order by Parent)
    WHERE  R.Distance >= 0 and R.ParentId is not null
    Order by R.ID, R.distance
    option(maxrecursion 10)

Results as follows:

    +----+--------+--------------+----------------------+
    | ID | Parent | DirectParent |  ReportingHierarchy  |
    +----+--------+--------------+----------------------+
    |  2 |      1 |            1 | Doe                  |
    |  3 |      1 |            1 | Mike                 |
    |  4 |      3 |            1 | Neil                 |
    |  4 |      1 |            0 | Mike > Neil          |
    |  5 |      4 |            1 | Sylvia               |
    |  5 |      3 |            0 | Neil > Sylvia        |
    |  5 |      1 |            0 | Mike > Neil > Sylvia |
    |  6 |      2 |            1 | Mary                 |
    |  6 |      1 |            0 | Doe > Mary           |
    +----+--------+--------------+----------------------+

EDIT: Should have probably mentioned this, but as per comment on this answer - make sure you have your ParentID and ID columns indexed.

Upvotes: 2

Related Questions