SQL query to get full hierarchy path

Question

Hi, I have a table with three columns NodeId, ParentNodeId, NodeName. for each node I would like to get a full path like "lvl1/lvl2/lvl3..." where lvl1,lvl2 and lvl3 are node names. I found a function which does that at this link http://www.sql-server-helper.com/functions/get-tree-path.aspx. but I would like to use CTE OR any other technique for efficiency. Please let me know if it is possible to achieve this in a better way. Thanks in advance.

Answer 1

Here's a CTE version.

declare @MyTable table (
    NodeId int,
    ParentNodeId int,
    NodeName char(4)
)

insert into @MyTable
    (NodeId, ParentNodeId, NodeName)
    select 1, null, 'Lvl1' union all
    select 2, 1, 'Lvl2' union all
    select 3, 2, 'Lvl3'

declare @MyPath varchar(100)

;with cteLevels as (
    select t.NodeId, t.ParentNodeId, t.NodeName, 1 as level
        from @MyTable t
        where t.ParentNodeId is null
    union all
    select t.NodeId, t.ParentNodeId, t.NodeName, c.level+1 as level
        from @MyTable t
            inner join cteLevels c
                on t.ParentNodeId = c.NodeId
)
select @MyPath = case when @MyPath is null then NodeName else @MyPath + '/' + NodeName end
    from cteLevels
    order by level

select @MyPath

Answer 2

The most efficient way, in my experience, would be to add an extra field, RootNodeID, containing the id of the toplevel node of the treestructure. So you can query all nodes in an entire treestructure in a very simple and efficient manner.

And to build that treestructure, a simple recursive function in your application should work just fine.

I know it's denormalization, and some people don't really approve of the concept, but I've learned in my professional experience that this brings a huge performance increase, as opposed to an elaborate t-sql script.

Answer 3

I solved it like this, much similar to Joe's solution.

    with cte (NodeId,NodeName,hierarchyPath)as
(
    select NodeId,NodeName, NodeName
    from Node
    where ParentNodeId is null 
    union all
    select n.NodeId, n.NodeName, CONVERT(varchar(256), cte.hierarchyPath + '/' + n.NodeName)
    from Node n
    join cte on n.ParentNodeId = cte.NodeId
)

select * 
from cte 
order by NodeId