Link to home
Start Free TrialLog in
Avatar of MSSystems
MSSystemsFlag for South Africa

asked on

Hierarchy structure

I recently came across a hierarchy structure with no integer value on the nodes. So I only have a structured string to work with. The problem is that I do not know how to convert the string into a legal node path and I do not want to rewrite SQL to accomplish this. Example:

\Node 1
\Node 1\Node 1
\Node 1\Node 2\Node 1
\Node 1\Node 2\Node 2
\Node 1\Node 1\Node 2

Please do not suggest a replace function on node. This is only an example.
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

How is the data supposed to be displayed?
Avatar of MSSystems

ASKER

Sorry, but basically this should be created as a Hierarchyid data type. Usually one accomplishes this with something like. Set HierarchyidCol = /1/1/2/1. It fails when you attempt something like Set HierarchyidCol = /Node 1/node 1/node 3/. Basically it is an unsupported format.
You did mentioned on the question, but why can't you just remove the "Node " string and replace the \ by /?

Something like this:

declare @h hierarchyid
declare @node nvarchar(50)
set @node = '\Node 1\Node 1\Node 2'
set @node = REPLACE(REPLACE(@node, 'Node ', ''),'\','/') + '/'
set @h = CAST(@node as hierarchyid)
select @h, cast(@h as nvarchar(500))

Open in new window

Maybe if you post some real sample data that will become clear.

Thanks.
I have attached some sample code below.
-- Create hierarchy structure
Create Table #SampleCode(
	[id] uniqueidentifier,
	[name] varchar(100),
	[ParentId] uniqueidentifier,
	[RowId] int IDENTITY(1,1) NOT NULL
)

Insert Into #SampleCode([id], [Name])
Values(NEWID(), 'a'),
	(NEWID(), 'b'),
	(NEWID(), 'c'),
	(NEWID(), 'd'),
	(NEWID(), 'e'),
	(NEWID(), 'f')

Update #SampleCode
Set [ParentId] = [pid].[id]
From #SampleCode [sc]
Join (  Select [id], [RowId] + 1 [prow]
		From #SampleCode) [pid] on [sc].[RowId] = [pid].[prow]

Insert Into #SampleCode([id], [name], [ParentId])
Select NEWID()
, [Name]+[name]
, [ParentId]
From #SampleCode
Where [RowId] > 1

Select [id], [name], [ParentId]
Into #hcyTable
From #SampleCode
	
Drop Table #SampleCode

-- use CTE to get full hierarchy path
;With Hierarchy([ID], [Name], [Level], [Path])
as
(	Select [hcyt].[id]
		, [hcyt].[name]
		, 0
		,Cast('/'+[hcyt].[name]+'/' as VarChar(MAX))
	From #hcyTable [hcyt] 
	Where [hcyt].[ParentId] is null
		Union all
	Select [hcyt].[id]
		, [hcyt].[name]
		,[cte].[Level]+1
        , Cast([cte].[Path] + [hcyt].[name]+'/' as VarChar(MAX))
	From #hcyTable [hcyt] 
	join Hierarchy [cte] on [cte].[ID] = [hcyt].[ParentId]
)

-- Here is the question. How do you get this path string to list as a proper hierarchyid.
Select [H].[path]
From [Hierarchy] as [H]
Order by [H].[path] 

Drop Table #hcyTable

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is great and exactly what I am looking for. However, is this the only way? Do you have to use this windowed function? Does SQL not allow you to convert directly from the path string?
I don't have much experience with hierarchyid, but I think as long as you can translate the string path into numbers (for example, using the RowId in your sample) you should be fine.  You could have a custom function that parses the string path and replaces the [name] by the corresponding [rowid] for example.  But it really depends on how do you plan to use the hierarchyid.  For instance, if the nodes should be arranged in a specific order you cannot use [rowid], because it will always add new nodes as the last child on the parent.

Here is a sample using just the [RowId]:
-- Create hierarchy structure
Create Table #SampleCode(
	[id] uniqueidentifier,
	[name] varchar(100),
	[ParentId] uniqueidentifier,
	[RowId] int IDENTITY(1,1) NOT NULL
)

Insert Into #SampleCode([id], [Name])
Values(NEWID(), 'a'),
	(NEWID(), 'b'),
	(NEWID(), 'c'),
	(NEWID(), 'd'),
	(NEWID(), 'e'),
	(NEWID(), 'f')

Update #SampleCode
Set [ParentId] = [pid].[id]
From #SampleCode [sc]
Join (  Select [id], [RowId] + 1 [prow]
		From #SampleCode) [pid] on [sc].[RowId] = [pid].[prow]

Insert Into #SampleCode([id], [name], [ParentId])
Select NEWID()
, [Name]+[name]
, [ParentId]
From #SampleCode
Where [RowId] > 1

-- use CTE to get full hierarchy path
;With Hierarchy([RowId], [ID], [Name], [Level], [Path], [HPath])
as
(	Select [hcyt].[RowId]
		, [hcyt].[id]
		, [hcyt].[name]
		, 0
		,Cast('/'+[hcyt].[name]+'/' as VarChar(MAX))
		,hierarchyid::GetRoot() as HPath
	From #SampleCode [hcyt] 
	Where [hcyt].[ParentId] is null
		Union all
	Select [hcyt].RowId
	    , [hcyt].[id]
		, [hcyt].[name]
		,[cte].[Level]+1
        , Cast([cte].[Path] + [hcyt].[name]+'/' as VarChar(MAX))
        ,CAST(cte.HPath.ToString() + CAST(hcyt.RowId as varchar(30)) + '/' as hierarchyid)
	From #SampleCode [hcyt] 
	join Hierarchy [cte] on [cte].[ID] = [hcyt].[ParentId]
)

-- Here is the question. How do you get this path string to list as a proper hierarchyid.
Select [H].[path], [H].HPath as hierarchyid, CAST([h].HPath as varchar(50)) hierarchyidstring
From [Hierarchy] as [H]
Order by [H].[path] 

Drop Table #SampleCode

Open in new window

Thank you for your help. I am now considering writing my own CLR function and hopefully get a performance increase.

Please let me know if you come across an alternate function.