Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 924
  • Last Modified:

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.
0
MSSystems
Asked:
MSSystems
  • 5
  • 4
1 Solution
 
wdosanjosCommented:
How is the data supposed to be displayed?
0
 
MSSystemsAuthor Commented:
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.
0
 
wdosanjosCommented:
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

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

Thanks.
0
 
MSSystemsAuthor Commented:
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

0
 
wdosanjosCommented:
I updated the code to generate the hierarchyid. Is this what you are looking for?

-- 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

Create Table #hcyTable
(
	id uniqueidentifier,
	name varchar(100),
	parentid uniqueidentifier,
	num int
)

Create Clustered Index #tmpind on #hcyTable (id, parentid)

Insert #hcyTable (id, name, parentid, num)
Select id, name, parentid, ROW_NUMBER() OVER (PARTITION BY parentid ORDER BY parentid)
From #SampleCode order by name
	
Drop Table #SampleCode

--select * from #hcyTable

-- use CTE to get full hierarchy path
;With Hierarchy([ID], [Name], [Level], [Path], [HPath])
as
(	Select [hcyt].[id]
		, [hcyt].[name]
		, 0
		,Cast('/'+[hcyt].[name]+'/' as VarChar(MAX))
		,hierarchyid::GetRoot() as HPath
	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))
        ,CAST(cte.HPath.ToString() + CAST(hcyt.num as varchar(30)) + '/' as hierarchyid)
	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], [H].HPath as hierarchyid, CAST([h].HPath as varchar(50)) hierarchyidstring
From [Hierarchy] as [H]
Order by [H].[path] 

Drop Table #hcyTable

Open in new window



It's based on the sample code on this page:
http://msdn.microsoft.com/en-us/library/bb630263%28v=SQL.100%29.aspx
0
 
MSSystemsAuthor Commented:
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?
0
 
wdosanjosCommented:
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

0
 
MSSystemsAuthor Commented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now