Solved

Hierarchy structure

Posted on 2011-02-13
9
914 Views
Last Modified: 2012-05-11
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
Comment
Question by:MSSystems
  • 5
  • 4
9 Comments
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
How is the data supposed to be displayed?
0
 
LVL 4

Author Comment

by:MSSystems
Comment Utility
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
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
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
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
Maybe if you post some real sample data that will become clear.

Thanks.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 4

Author Comment

by:MSSystems
Comment Utility
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
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
Comment Utility
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
 
LVL 4

Author Comment

by:MSSystems
Comment Utility
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
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
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
 
LVL 4

Author Comment

by:MSSystems
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now