Sql query for tree table

Expanding on a_horse_with_no_name’s answer, this show how to use SQL Server’s implementation of recursive CTE (recursive single-record cross apply) in combination with row_number() to produce the exact output in the question.

declare @t table(id int,parentId int,name varchar(20))
insert @t select 1,  0        ,'Category1'
insert @t select 2,  0,        'Category2'
insert @t select 3,  1,        'Category3'
insert @t select 4 , 2,        'Category4'
insert @t select 5 , 1,        'Category5'
insert @t select 6 , 2,        'Category6'
insert @t select 7 , 3,        'Category7'

WITH tree (id, parentid, level, name, rn) as 
   SELECT id, parentid, 0 as level, name,
       convert(varchar(max),right(row_number() over (order by id),10)) rn
   FROM @t
   WHERE parentid = 0


   SELECT c2.id, c2.parentid, tree.level + 1, c2.name,
       rn + "https://stackoverflow.com/" + convert(varchar(max),right(row_number() over (order by tree.id),10))
   FROM @t c2 
     INNER JOIN tree ON tree.id = c2.parentid
FROM tree
ORDER BY cast("https://stackoverflow.com/" + RN + "https://stackoverflow.com/" as hierarchyid)

To be honest, using the IDs themselves to produce the tree “path” would work, since we are ordering directly by id, but I thought I’d slip in the row_number() function.

Leave a Comment