Recursive parentIDs in Path Method Hierarchy, Sql Server

Language: Layout:

create table tmp (deptName varchar(32),deptID int,deptHier varchar(32))
      insert into tmp (deptName,deptID,deptHier) values
          ('a',1,'1')
        , ('b',2,'1.2')
        , ('c',3,'1.2.3');
    go

create function dbo.DelimitedSplitN4K (
        @pString nvarchar(4000)
      , @pDelimiter nchar(1)
      )
    returns table with schemabinding as
    return
      with e1(n) as (
        select 1 union all select 1 union all select 1 union all 
        select 1 union all select 1 union all select 1 union all 
        select 1 union all select 1 union all select 1 union all select 1
      )
      , e2(n) as (select 1 from e1 a, e1 b)
      , e4(n) as (select 1 from e2 a, e2 b)
      , cteTally(n) as (select top (isnull(datalength(@pString)/2,0))
          row_number() over (order by (select null)) from e4)
      , cteStart(n1) as (select 1 union all 
          select t.n+1 from cteTally t where substring(@pString,t.n,1) = @pDelimiter)
      , cteLen(n1,l1) as(select s.n1
      ,   isnull(nullif(charindex(@pDelimiter,@pString,s.n1),0)-s.n1,4000)
        from cteStart s
      )
     select ItemNumber = row_number() over(order by l.n1)
          , Item       = substring(@pString, l.n1, l.l1)
       from cteLen l;
    go

select t.*,x.*
      from tmp t
        cross apply (
          select DeptHierName = stuff((
            select '.'+p.deptName 
              from tmp P 
                inner join dbo.DelimitedSplitN4K(t.deptHier,'.') s 
                  on p.deptId = s.Item
              order by ItemNumber
              for xml path (''), type).value('.','varchar(max)'),1,1,'')) x

View schema
Execution time: 0,03 sec, rows selected: 3, rows affected: 3, absolute service time: 0,2 sec