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.

Answer 1

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..

Answer 2

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

Answer 3

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.