CTE Recursion to get hierarchy level with Empty Level

Question

I have table structure below in SQL Server 2012

INSERT INTO tblLocations (Relation, ParentID, Name, Levelnumber, Code) 
VALUES
    ('Parent', NULL, 'West',0,'X'),
    ('Child', 1, 'WA',1,'X'),
    ('Grandchild', 2, 'Seattle',2,'X'),
    ('Grandchild', 2, 'Seattle2',2,'X'),
    ('GreatGrandchild', 3, 'aa',3,'X'),
    ('GreatGrandchild', 3, 'bb',3,'X'),
    ('Parent', NULL, 'MidWest',0,'Y'),
    ('Child', 7, 'IL',1,'Y'),
    ('Grandchild', 8, 'Chicago',2,'Y'),
    ('Grandchild',8, 'Chicago1',2,'Y'),
    ('GreatGrandchild', 9, 'cc',3,'Y'),
    ('GreatGrandchild', 9, 'dd',3,'Y'),
    ('Parent', NULL, 'South',0,'Z'),
    ('Child', 13, 'TX',1,'Z'),
    ('GreatGrandchild', 14, 'ff',3,'Z'),
    ('GreatGrandchild', 14, 'ff',3,'Z'),
    ('Parent', NULL, 'North',0,'A'),
    ('Grandchild',17, 'Chicago1',2,'A'),
    ('GreatGrandchild', 18, 'ff',3,'A'),
    ('GreatGrandchild', 18, 'ff',3,'A');

As we can see some of the levels don't exist for all nodes. Our requirement is to get all levels for all nodes.

For example, for code X

Parent -> Child -> GrandChild --> GreatGrandchild 

hierarchy is there.. but for code A, we have

Parent -> GrandChild -> GreatGrandChild

Similarly for Code Z:

Parent -> Child -> GreatGrandChild

Our requirement is, if one level is not there, then the subsequent level (identified by level number) should be populated for the missing level. We have only 4 levels..

We always need

Parent -> Child (if child is not there search down (Grandchild / GreatGrandchild) until data is there and populate as Child) 
       -> GrandChild - > GreatGrandChild

This is the CTE we got,

WITH Hierarchy AS 
( 
    -- initialization 
    SELECT Relation, ParentID, Name, Levelnumber, Code
    FROM tblLocations
    WHERE LevelNumber = 0

    UNION ALL 

    -- recursive execution 
    SELECT S.Relation, S.ParentID,S.Name,S.Levelnumber, S.Code
    FROM tblLocations S 
    INNER JOIN tblLocations T ON T.Id = S.ParentId 
) 
SELECT * 
FROM Hierarchy 
WHERE Code = 'X' ;

We need it in a view, so no T-SQL.

Kindly guide

Answer 1

You have a problem in your recursive query in that it's not recursive. Recursive queries, by definition, are self referential. The way you've written the query, the portion that should define the recursion is just a normal join of the table to itself.

That said, all is not lost. Here's what I came up with:

WITH Hierarchy AS 
( 
--initialization 
SELECT ID, 
    Relation, 
    ParentID, [Name], 
    Levelnumber, 
    Code, 
    1 AS [__level__],
    cast(concat('/', ID, '/') as varchar(max)) as h
FROM tblLocations
WHERE LevelNumber = 0
UNION ALL 
--recursive execution 
SELECT child.ID,
    child.Relation,
    child.ParentID, 
    child.Name, 
    child.Levelnumber, 
    child.Code, 
    parent.[__level__] + 1,
    cast(concat(parent.h, child.ID, '/') as varchar(max)) as h
FROM tblLocations child
INNER JOIN Hierarchy AS parent
    ON parent.Id = child.ParentId 
) 
SELECT *, 
    choose(
        Hierarchy.[__level__], 
        'Parent', 
        'Child', 
        'GrandChild', 
        'GreatGrandchild'
    ) as [DerivedRelation]
FROM Hierarchy 
WHERE Code = 'A' 
order by h;

The actual recursive query is fairly standard hierarchy traversal stuff. To meet your requirement, I'm computing my own concept of where I'm at in the hierarchy so that I can use that to determine what you want to display as the relation. You didn't say what version of SQL you're on, so you may not have concat() and choose(). That's okay though; they're just syntactic sugar around string + string and case statements.

One other thing that I'll note here is the h column. I've written my fair share of hierarchical queries and for the actual run-time execution of them, I prefer hierarchyid instead. You pay a little in the maintenance of the data, but working with them for queries is fairly performant (since you can index the hierarchy and say things like where h.IsDescendentOf(some_other_hierarchyID). All of that is to say that the h column is directly cast-able to a hierarchyid should you want to go that direction.