SQLTeam.com | Weblogs | Forums

Show folder hierarchy



In VMWare world there is a sql server database backend for it. I am trying to sort out a simple way of capturing folder hierarchy for each virtual machine. You can have a top node folder name and subfolders and so on and so forth.

The final result I would like to see is
vmname folder
Menelik \ንግሥተ፡ሳባእ
Ijigayehu \ንግሥተ፡ሳባእ\Menelik

create table #ancestry(id int not null, name nvarchar(50), parentid int null)
insert into #ancestry
select 1, N'ንግሥተ፡ሳባእ', null union
select 2, 'Menelik', 1 union
select 3, 'Bezabeh', 1 union
select 4, 'Ijigayehu', 2 union
select 5, 'Tewodros', 4

select * 
  From #ancestry

drop table #ancestry


Is this what you seek:

with cte
  as (select id
            ,cast([name] as nvarchar(max)) as [name]
            ,0 as [level]
        from #ancestry as a
       where parentid is null
      union all
      select b.id
            ,cast(a.[name]+'\'+b.[name] as nvarchar(max)) as [name]
            ,a.[level]+1 as [level]
        from cte as a
             inner join #ancestry as b
                     on b.parentid=a.id
select *
  from cte


oh great Alchemists bitsmed, yes this is what I seek. you have given me the elixir of life!

wow Thanks dude!