Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Hierarchy structure

Posted on 2011-02-13
9
Medium Priority
?
921 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34883651
How is the data supposed to be displayed?
0
 
LVL 4

Author Comment

by:MSSystems
ID: 34886557
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
ID: 34887736
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 23

Expert Comment

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

Thanks.
0
 
LVL 4

Author Comment

by:MSSystems
ID: 34905591
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 2000 total points
ID: 34909116
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
ID: 34909806
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
ID: 34910321
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
ID: 34910412
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 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