Solved

Hierarchy structure

Posted on 2011-02-13
9
916 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL - Rotating Values in SQL 9 68
Managing Columnstore Indexes 2 32
Sql query 107 64
Anyway to make these 2 SQL statements into one? 13 39
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 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