Is there a way to create tree view of sqlserver hierarchyid data using t-sql, SSMS, or python?

Question

I have a sql server table containing hierarcyid data type. Nodes are represented as '/' as the top node, '/1/', '1/2/', '/1/2/3/'... etc as the descendent nodes.

I am looking for a way to display this data so you can see the levels of indenture.

Tabs would be ok, a tree structure would be even better. Thanks!

I have done some experiments with converting the 'level' to a string containing number of spaces, and printing that. It's better than nothing, but if there is something better, either using SSMS, a stored procedure, or python that would be excellent.

Eventually would like the ability to add, remove, and move nodes. For now, something quick and dirty will work, just want to show the customer what it looks like.

Thanks!

Answer 1

Here is a small working sample

Example

Declare @YourTable table (ID int,Pt Int,Label varchar(50),HierID hierarchyid)
Insert Into @YourTable values
 (1,null,'Top','/1/')
,(2,1,'Some Item A','/1/2/')
,(3,1,'Some Item B','/1/3/')
,(4,3,'Some Item C','/1/3/4/')
,(5,1,'Some Item D','/1/5/')
,(6,3,'Some Item E','/1/3/6/')

Select Lvl   = HierID.GetLevel()
      ,ID
      ,PT
      ,Label  = replicate('|----',HierID.GetLevel()-1) + Label  -- Nesting Optional ... For Presentation
      ,HierID_String = HierID.ToString()
 From @YourTable A
 Order By A.HierID

Results