SQL Recursive Query on hirachical name

Question

New to MS SQL. Having the table like as follows,

ID  Name    Parent ID
1   AA      0
2   BB      1
3   CC      2
4   DD      3
5   EE      3
6   FF      4
7   FG      6
8   AD      1
9   AC      2

My expected Result is showed in the Hierarchical Name

ID  Name    Parent ID   Hirarchical Name
1   AA      0           AA
2   BB      1           AA.BB
3   CC      2           AA.BB.CC
4   DD      3           AA.BB.CC.DD
5   EE      3           AA.BB.CC.EE
6   FF      4           AA.BB.CC.DD.FF
7   FG      6           AA.BB.CC.DD.FF.FG
8   AD      1           AA.AD
9   AC      2           AA.BB.AC

Thanks for your help in advance.

i have tried with inner join and union all.. but its not giving me the help. — P Kumar, Mar 09 '19 at 09:59
Possible duplicate of [Simplest way to do a recursive self-join?](https://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join) — zmag, Mar 09 '19 at 10:12

score 0 · Answer 1 · answered Mar 09 '19 at 13:27

Recursive CTEs are a bit tricky. This does what you want:

with cte as (
      select id, name, convert(varchar(max), name) as hier, 1 as lev
      from t
      where parentid = 0
      union all
      select t.id, t.name, cte.hier + '.' + t.name, lev + 1
      from cte join
           t
           on t.parentid = cte.id
      where lev < 10
     )
select *
from cte;

Here is a db<>fiddle.

SQL Recursive Query on hirachical name

1 Answers1