Retrieve the path of a node

Hi Experts,

I have a table like this:
CategoryId
ParentCategoryId
CategoryName

I have a categoryId and I want to list all the parents up the root category.

I want to find all the parents to get the complete path.
Example:
Id      Name     ParentId
0       root       null
1       node1    0
2       node2    1
3       node3    0
4       node4    2
I want to find the path of node4 which result is:
root/node1/node2/node4

Thanks in advance for your help.
LVL 9
nouloukAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
see here for how to implement a helper function for that:
http://www.sql-server-helper.com/functions/get-tree-path.aspx
0
 
nouloukAuthor Commented:
Thanks  angelIII,

your link gives me a solution if I add a new level column to my table.
Is it possible to do this  using CTE recursion ?
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>your link gives me a solution if I add a new level column to my table.
?? I don't see such a column in the code ? just ID and parentID ...

>Is it possible to do this  using CTE recursion ?
yes, that should be possible:


declare @id int
set @id = 4
 
declare @path varchar(max)
with p ( id, parentid, name, level )
as (
  select id, parentid, name, 0 as level
  from yourtable 
  where id = @id
 UNION ALL
   select t.id, t.parentid, t.name, p.level + 1
   from yourtable t
   join p 
     on p.parentid = t.id
  )
select @path = coalesce(@path + '/') + name
from  p
order by p.level desc

Open in new window

0
 
nouloukAuthor Commented:
Perfect.
0
All Courses

From novice to tech pro — start learning today.