Recursive parentIDs

Question

I have a table that include department data and it has the following structure,

+----------+--------+----------+
| deptName | deptID | deptHier |  
+----------+--------+----------+
| a        |      1 |        1 |  
| b        |      2 |      1.2 |  
| c        |      3 |    1.2.3 |  
+----------+--------+----------+

The deptHier column has all the parent's id and it's own id.

I would like to get all the parent's name including the child's name. Something like this,

+----------+--------+----------+--------------+
| deptName | deptID | deptHier | deptHierName |
+----------+--------+----------+--------------+
| a        |      1 |        1 | a            |
| b        |      2 |      1.2 | a.b          |
| c        |      3 |    1.2.3 | a.b.c        |
+----------+--------+----------+--------------+

I have tried using the methods with 1 id in the deptHier and try to split it along the way, but I'm not even close. If anyone can help that would be great. Thanks in advance.

Answer 1

rextester: http://rextester.com/KKLDZ64185

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

String Splitting Function:

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

Query:

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

returns the following:

+----------+--------+----------+--------------+
| deptName | deptID | deptHier | deptHierName |
+----------+--------+----------+--------------+
| a        |      1 |        1 | a            |
| b        |      2 |      1.2 | a.b          |
| c        |      3 |    1.2.3 | a.b.c        |
+----------+--------+----------+--------------+

splitting strings reference:

• Tally OH! An Improved SQL 8K “CSV Splitter” Function
• Splitting Strings : A Follow-Up - Aaron Bertrand
• Split strings the right way – or the next best way

Answer 2

Option without a Parse function

Declare @YourTable table (deptName varchar(50),deptID int,deptHier varchar(50))
Insert Into @YourTable values
('a',1,'1'),
('b',2,'1.2'),
('c',3,'1.2.3')

Select A.*
 From  @YourTable A
 Cross Apply (
         Select deptHierName  = Stuff((Select '.' +deptName 
         From (
                 Select Top 100 Percent *
                  From (
                         Select RetSeq = Row_Number() over (Order By (Select null))
                               ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                         From  (Select x = Cast('<x>'+ replace((Select A.deptHier as [*] For XML Path('')),'.','</x><x>')+'</x>' as xml).query('.')) as X 
                         Cross Apply x.nodes('x') AS B(i)
                       ) X
                 Join @YourTable D on (X.RetVal=D.deptID)
                 Order By RetVal
              ) S
          For XML Path ('')),1,1,'')
 ) B

Returns