I have a table with hierarchyid column. It is like:
[NAME] [PATH]
Ahmet /
Aliye /1/
Selen /1/1/
Erdem /2/
Bilge /2/1/
Aydin /2/2/
Tomrs /2/2/2/
I want to see NAMES like:
[NAMES_WITH_HIERARCHY]
Ahmet
Ahmet/Aliye
Ahmet/Aliye/Selen
Ahmet/Erdem
Ahmet/Erdem/Bilge
Ahmet/Erdem/Aydin
Ahmet/Erdem/Aydin/Tomrs
How can i do this?
Here you go:
declare @hierarchy table (name varchar(20), [path] hierarchyid)
insert into @hierarchy ( name, path )
values
('Ahmet', '/')
,('Aliye', '/1/')
,('Selen', '/1/1/')
,('Erdem', '/2/')
,('Bilge', '/2/1/')
,('Aydin', '/2/2/')
,('Tomrs', '/2/2/2/')
--select * from @hierarchy as h
;with Tree([level], [FullName], [path]) as (
select h.[path].GetLevel() as [level], cast(h.[name] as varchar(max)), h.[path]
from @hierarchy as h
where [path] = '/'
union all
select h2.[path].GetLevel(), t.[FullName] + '/' + h2.[name] , h2.[path]
from Tree t
join @hierarchy as h2 on h2.[path].IsDescendantOf(t.[path]) = 1 and t.[path] <> h2.[path] and h2.[path].GetLevel() - t.[level] < 2
)
select [Level], cast(FullName as varchar(25)) [Fullname], cast(Path as varchar(10)) [Path]
from Tree
order by Path
Output:
Level Fullname Path
------ ------------------------- ----------
0 Ahmet /
1 Ahmet/Aliye /1/
2 Ahmet/Aliye/Selen /1/1/
1 Ahmet/Erdem /2/
2 Ahmet/Erdem/Bilge /2/1/
2 Ahmet/Erdem/Aydin /2/2/
3 Ahmet/Erdem/Aydin/Tomrs /2/2/2/