Show all Children and Grandchildren in SQL Hierarchy CTE

Question

I have a hierarchy in SQL Server, with multiple parents, but can't seem to get the result set I need.

This is what i have so far.

DECLARE @Table TABLE (ChildId varchar(max), ParentId varchar(max))
INSERT INTO @Table (ChildId,ParentId)
VALUES  
        ('England',NULL),
        ('Cities',NULL),
        ('Towns',NULL),
        ('South West','England'),
        ('Bristol','South West'),
        ('Bristol','Cities'),
        ('Suburb','Bristol'),
        ('Thornbury','South West'),
        ('Thornbury','Towns');


WITH CTE (ChildId, ParentId, Level)
AS ( 
        SELECT 
            ChildId,
            ParentID,
            0
        FROM @Table 
        WHERE ParentID IS NULL
        UNION ALL

        SELECT 
            r.ChildId,
            r.ParentId,
            ct.Level + 1
        FROM @Table r
        JOIN CTE ct
        ON ct.ChildId = r.ParentId

    )

SELECT * FROM CTE order by childId, level

Which gives me this result set:

ChildId    | ParentId   | Level
Bristol    | Cities     | 1
Bristol    | South West | 2
Suburb     | Bristol    | 2
Suburb     | Bristol    | 3
Cities     | NULL       | 0
England    | NULL       | 0
South West | England    | 1
Thornbury  | Towns      | 1
Thornbury  | South West | 2
Towns      | NULL       | 0

But I also want grand parents and great grand parents and great great grandparents (etc):

ChildId    | ParentId   | Level
Bristol    | Cities     | 1
Bristol    | South West | 2
Bristol    | England    | <------------------------
Suburb     | South West | <------------------------ 
Suburb     | England    | <------------------------
Suburb     | Cities     | <------------------------

etc.

possible duplicate of [How to retrieve hierarchical data from a SQL Table?](http://stackoverflow.com/questions/11230693/how-to-retrieve-hierarchical-data-from-a-sql-table). Have a look at the selected answer in that QA. Might be what you are looking for with CTE's — Bernd Linde, Nov 20 '14 at 16:56
No, that is a standard hierarchy SQL question, of which there are many examples. I specifically need all the links (Grand Children, Great Grand Children) like the question states - please remove the duplicate flag — Paul Grimshaw, Nov 20 '14 at 16:59
possible duplicate of [Select statement to return parent and infinite children](http://stackoverflow.com/questions/25550850/select-statement-to-return-parent-and-infinite-children) — Tanner, Nov 20 '14 at 17:00
That question is also different, and in fact the accepted answer is IN my question already, the relationship being my level. I'm looking for more results, separate rows for separate relationships (child, grand child etc) — Paul Grimshaw, Nov 20 '14 at 17:04

Roger Wolf · Accepted Answer · 2014-11-21T15:13:13.440

4

What you are trying to do resembles, at least to some extent, the Ranganathan's classification. In this case you have to go up in hierarchy, not down:

with cte as (
    select t.ChildId, t.ParentId, 0 as [Lvl]
    from @Table t
    where t.ParentId is not null
    union all
    select c.ChildId, t.ParentId, c.Lvl + 1
    from @Table t
        inner join cte c on c.ParentId = t.ChildId
    where t.ParentId is not null
)
select * from cte c order by c.ChildId, c.Lvl, c.ParentId;

EDIT: updated the WHERE clause in the recursive part of the CTE. Looks like it was some leftover from initial tries, which I forgot to think through..

edited Nov 21 '14 at 15:13

answered Nov 20 '14 at 23:05

Roger Wolf

7,307
2
24
33

Well done! More concise and probably more efficient solution than mine. I fail to understand the purpose of c.ParentId != t.ParentId in the where clause though. – Giorgos Betsos Nov 21 '14 at 10:03
@GiorgosBetsos, you are right. I updated the answer. – Roger Wolf Nov 21 '14 at 15:13

Giorgos Betsos · Answer 2 · 2014-11-20T20:54:15.730

What about using a recursive table-valued function instead of a CTE:

CREATE FUNCTION tvf_GetParents 
(   
    @childID VARCHAR(MAX),
    @level INT
)
RETURNS 
@output TABLE 
(
    ancestor VARCHAR(MAX),
    level INT
)
AS
BEGIN

    DECLARE @parentIDs TABLE (pID VARCHAR(MAX))

    -- Get parent of child and add it to output
    IF EXISTS (SELECT 1 FROM HTable WHERE ChildId = @childID AND ParentId IS NOT NULL)
    BEGIN
       INSERT @parentIDs
       SELECT ParentId FROM HTable WHERE ChildId = @childID

       INSERT INTO @output (ancestor, level)
       SELECT pID, @level FROM @parentIDs
    END    
    ELSE
       RETURN

    DECLARE @pID VARCHAR(MAX) = 0

    -- Iterate over all parents (cursorless loop)
    WHILE (1 = 1) 
    BEGIN  

        -- Get next ParentId
        SELECT TOP 1 @pID = pID
        FROM @parentIDs
        WHERE pID > @pID 
        ORDER BY pID

        -- Exit loop if no more parents
        IF @@ROWCOUNT = 0 BREAK;

        -- call function recursively so as to add to output 
        -- the rest of the ancestors (if any)
        INSERT INTO @output (ancestor, level)
        SELECT ancestor, level FROM tvf_GetParents(@pID, @level + 1) 
    END

    RETURN 
END
GO

Using the above function you can easily get all child - ancestor pairs:

SELECT DISTINCT ChildId, ancestor, level
FROM HTable h 
OUTER APPLY tvf_GetParents(h.ChildId, 0) AS p
ORDER BY ChildId, Level

Output:

ChildId    ancestor     level
------------------------------
Bristol    Cities       0
Bristol    South West   0
Bristol    England      1
Cities     NULL         NULL
England    NULL         NULL
South West England      0
Suburb     Bristol      0
Suburb     Cities       1
Suburb     South West   1
Suburb     England      2
Thornbury  South West   0
Thornbury  Towns        0
Thornbury  England      1
Towns      NULL         NULL

Please note that 'Level' has a different meaning here: level NULL denotes a parent-less child, level 0 denotes a child-parent record, level 1 denotes a child-grandparent record, etc.

Please also note that there is a limitation as far as nesting level of recursive functions in sql server is concerned. I think it is 32. If your tree depth goes beyond that range then the solution I propose will not work.

Thanks, but prefer the CTE version, more concise and @Roger Wolf has given a solution — Paul Grimshaw, Nov 21 '14 at 09:50

score -3 · Answer 3 · answered Nov 20 '14 at 21:18

Is this what you're looking for?

WITH CTE (ChildId, FirstChild, ParentId, Level)
AS ( 
        SELECT 
            ChildId,
            ChildId as FirstChild,
            ParentID,
            0
        FROM @Table 
        WHERE ParentID IS NULL
        UNION ALL
        SELECT 
            r.ChildId,
            ct.FirstChild,
            r.ParentId,
            ct.Level + 1
        FROM @Table r
        JOIN CTE ct
        ON ct.ChildId = r.ParentId

    )
SELECT ChildId,
    ParentId,
    Level
FROM CTE
UNION
SELECT FirstChild,
    ParentId,
    Level
FROM CTE
ORDER BY ChildId,
    Level,
    ParentId

Output:

ChildId      ParentId     Level
-------      --------     -----
Bristol      Cities       1
Bristol      South West   2
Cities       NULL         0
Cities       Cities       1
Cities       Bristol      2
England      NULL         0
England      England      1
England      South West   2
England      Bristol      3
South West   England      1
Suburb       Bristol      2
Suburb       Bristol      3
Thornbury    Towns        1
Thornbury    South West   2
Towns        NULL         0
Towns        Towns        1

I don't think the above output is the correct one: e.g. England is a top level record. It should appear only once in the output. — Giorgos Betsos, Nov 20 '14 at 21:32

Show all Children and Grandchildren in SQL Hierarchy CTE

3 Answers3