Add root node to SQL CTE Hierarchy Tree

Question

I have a query working that shows my hierarchy tree based on CTE Recursion to get tree hierarchy.

Results look like

- Category
- Category
- - SubCategory
- - - Sub SubCategory
- - SubCategory
...

I want to add a root node to my tree, but I'm not seeing how to do this. How can I add the root node to this tree?

EDIT: My current query looks like this:

;WITH Tree as
(
    Select Parent_ID, ParentDescr, Node_ID, Node, 1 as Level, cast(Node as varchar(max)) as Path
    FROM Nodes
    WHERE ParentDescr = 'Root'

    UNION ALL

    Select  A.Parent_ID, A.ParentDescr, A.Node_ID, A.Node, Level + 1 as Level, cast(B.Path + ' | ' + A.Node as varchar(max)) as Path
    FROM Nodes A
    INNER JOIN Tree B ON A.PARENT_ID = B.Node_ID
    WHERE A.ParentDescr <> 'Root'
) 
SELECT  REPLICATE(' - ', level) + Node as Tree, Path, Node_ID, ParentDescr, PARENT_ID
from Tree
ORDER BY Path asc

Here is some sample data:

SELECT * 
FROM (
VALUES 
('123','Root',NULL,NULL),
('456','Category','123','Root'),
('789','SubCategory','456','Category'),
('012','Sub SubCategory','789','SubCategory'),
('345','Category','123','Root')) AS vtable 
([Class_ID],[Class],[Parent_ID],[ParentClass])

Results should look like:

Root
- Category
- - SubCategory
- - - Sub SubCategory
- Category

What's wrong with the code you have posted? Are you getting an error? — Tab Alleman, Dec 30 '15 at 20:26
Looks to me like you've got it, since the top half of the union uses `ParentDescr = 'Root'`. So what is wrong with what you are getting? — Tab Alleman, Dec 30 '15 at 20:28

Tab Alleman · Accepted Answer · 2015-12-30T21:25:53.477

Actually, I think you just need to change this:

WHERE ParentDescr = 'Root'

To this:

WHERE Node = 'Root'

And, similarly, change this:

WHERE A.ParentDescr <> 'Root'

To this:

WHERE A.Node <> 'Root'

EDIT:

Based on your sample data, my changes should have worked if you implemented them correctly. Verify that your new query looks like this:

;WITH Tree as
(
    Select Parent_ID, ParentDescr, Node_ID, Node, 1 as Level, cast(Node as varchar(max)) as Path
    FROM Nodes
    WHERE Node = 'Root'

    UNION ALL

    Select  A.Parent_ID, A.ParentDescr, A.Node_ID, A.Node, Level + 1 as Level, cast(B.Path + ' | ' + A.Node as varchar(max)) as Path
    FROM Nodes A
    INNER JOIN Tree B ON A.PARENT_ID = B.Node_ID
    WHERE A.Node <> 'Root'  --this line really isn't even necessary
) 
SELECT  REPLICATE(' - ', level) + Node as Tree, Path, Node_ID, ParentDescr, PARENT_ID
from Tree
ORDER BY Path asc

It should work.

Gonna have to see your raw data then. Guessing at it isn't cutting it. — Tab Alleman, Dec 30 '15 at 20:39

Add root node to SQL CTE Hierarchy Tree

1 Answers1