Hi all,

I just started learning HierarchyID and copied the the following code from http://stackoverflow.com/questions/443930/generating-hierarchyid and Chapter 7 Hierarchical Data and the Relational Database of the book "Programming Microsoft SQL Server 2012", written by L. G. Lobel and A. J. Brust (Published by Microsoft Press):

--  shcHierarchyid_1.sql
--   19 August 2015  13:15 PM

USE master
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'shcDB')
 DROP DATABASE shcDB 
GO
CREATE DATABASE shcDB
GO

USE shcDB
GO

CREATE TABLE OrgChart
(
  Position hierarchyid,
  Title nvarchar(50)
)

INSERT into OrgChart(Position, Title)
   values (hierarchyid::Parse('/'), 'CEO'),
          (hierarchyid::Parse('/1/'), 'Purchase Manager'),
	      (hierarchyid::Parse('/1/1/'), 'Purchase Executive'),
          (hierarchyid::Parse('/2/'), 'Sales Manager'),
          (hierarchyid::Parse('/2/1/'), 'Sales Executive')

SELECT Position.ToString(), * FROM OrgChart

/*
	C L E A N U P
*/

DROP TABLE OrgChart
GO

I executed the code in my Microsoft SQL Server 2012 Management Studio (SSMS2012).  It worked nicely. But, I don't understand the following 2 things:

#1: In the Results, I got:           (No column name)     Position    Title

                                         1       /                                            CEO

                                         2      /1/                         0x58         Purchase Manager

                                         3      /1/1/                      0x5AC0     Purchase Executive

                                         4      /2/                         0x68         Sales Manager

                                         5     /2/1/                       0x6AC0     Sales Executive

   I don't know how to give a column name for the /, /1/, /1/1/, /2/, /2/1/ (NodePath (?) I guessed). Please tell me how I can give a column name to that column.

#2:   The book lists "USE master  GO .....".  I wonder whether I can do the "hierarchyid" thing in the private database I created!!??  Please tell me whether I can do the "hierarchyid" thing in the private database or not.

Please kindly help, advise and respond.

Thanks in advance,

Scott Chang   

• Edited by Scott H Chang Wednesday, August 19, 2015 7:22 PM

Hi Naomi and Tom,  Thanks for your nice responses.

I tried what you said. It worked for me.

I also tried to do/lear the following modified code in my personal database (ScottChangDB) of SSMS2012:

--  shcHierarchyid_3.sql (Note: Using DECLARE, SET, SELECT, INSERT to do the task)
--   20 August 2015  07:30 PM

USE ScottChangDB
GO

/*
IF EXISTS(SELECT name FROM ScottChangDB WHERE name = 'hierarchyidDB')
 DROP DATABASE hierarchyidDB 
GO
CREATE DATABASE hierarchyidDB
GO

USE hierarchyidDB
GO
*/

CREATE TABLE OrgChart
(
  Position hierarchyid,
  Title nvarchar(50)
)

declare @root hierarchyid,
        @id hierarchyid

set @root = hierarchyid::GetRoot()

insert into OrgChart(Position, Title) values (@root, 'CEO')

set @id = @root.GetDescendant(null, null)
insert into OrgChart(Position, Title) values (@id, 'Purchase Manager')

set @id = @root.GetDescendant(@id, null)
insert into OrgChart(Position, Title) values (@id, 'Sales Manager')

select @id = Position.GetDescendant(null, null) from OrgChart where Title = 'Purchase Manager'    
insert into OrgChart(Position, Title) values (@id, 'Purchase Executive')

select @id = Position.GetDescendant(null, null) from OrgChart where Title = 'Sales Manager'    
insert into OrgChart(Position, Title) values (@id, 'Sales Executive')

I executed the modified code successfully.  But, I got the following result in the dbo.OrgChart table of my ScottChangDB:

                                           Position        Title

                                           /                   CEO

                                           /1/                Purchase Manager

                                           /2/                Sales Manager

                                           /1/1/             Purchase Executive

                                           /2/1/             Sales Executive

I have the following things/questions to ask you:

Question #1:  I want to get the following result in the dbo.OrgChart from a new set of modified code statements:

                                NodePath     Position      TiTle

                                  /                                   CEO

                                  /1/               xxx            Purchase Manager

                                  /2/               yyy            Sales Manager

                                 /1/1/             xxxzz         Purchase Executive

                                 /2/1/             yyyzz         Sales Executive

Is it possible to get such kind of the result from the modified code? if possible, how can I do it?

Question #2:  I really don't understand the following code statements:

(i) set @root = hierarchyid::GetRoot(),

(ii) set @id=@root.GetDescentdant(null,null), and

(III) insert into OrgChart(Position, Title) values (@id, 'xyz')

Please explain these 3 code statements. Is it possible to have the NodePath shown in the result of the dbo.OrgChart table from these code statements?

Question #3:  I am going to drop the dbo.OrgChart in my ScottChangDB database, atfer I have Questions #1 and #2 answered, and the newly revised code statements from you,  How can I drop the existing TABLE OrgChart in my ScottChangDB, before the revised code statements executed?  Note: I have difficulties to do this kind of dropping thing  shcHierarchyis_3.sql - see the "Comment" block of the code presented above.

Please kindly help, answer my Questions (enlighten me in learning HierarchyID) and respond again.

Many Thanks,

Scott Chang 



Hi Scott,

Question #1
Not sure in what logic you set the xxx,yyy etc, please see if the below sample meets what your require.

;WITH Cte AS
(
--Get top parent node
SELECT Position,CAST('/'+Title AS VARCHAR(MAX)) fullpath,Title FROM OrgChart WHERE position.GetLevel()=0
UNION ALL
SELECT o.Position,CAST(c.fullpath+'/'+o.title AS VARCHAR(MAX)),o.title FROM OrgChart o JOIN Cte c 
ON o.Position.GetAncestor(1)=C.Position 
)
SELECT Position.ToString() NodePath,fullpath,title FROM Cte

It is a technology called recurrsive CTEcalled which iterates hierarchy structure in your table from top to leaves in this case.

Question #2

(i) set @root = hierarchyid::GetRoot()
  Returns the root of the hierarchy tree, try "SELECT hierarchyid::GetRoot()", see more details on GetRoot

(ii) set @id=@root.GetDescentdant(null,null), and

  GetDescentdant returns a child node of the parent. See more details on GetDescendant.

(III) insert into OrgChart(Position, Title) values (@id, 'xyz')
  This is a insert statement. Position is hierarchyid type so you have to insert a value matching that type, the
  variable @id is generated by the function in (ii).

Question #3
  You can drop the table with "DROP TABLE OrgChart;". Can you elaborate what is the difficulty?

If you have any question, feel free to let me know.

Hi Eric, Thanks for your nice response.

I tried your CTE thing:

--  shcHierarchyid_3.sql (Note: Using DECLARE, SET, SELECT, INSERT to do the task)
--   20 August 2015  07:30 PM // Added SELECT Position.ToString() AS NodeidPath, * FROM OrgChart (14:55 PM)
--   24 August 2015   8:45 AM  (Tried to do - Eric Zhang: WITH Cte AS ....)

USE ScottChangDB
GO

DROP TABLE OrgChart

CREATE TABLE OrgChart
(
  Position hierarchyid,
  Title nvarchar(50)
)

declare @root hierarchyid,
        @id hierarchyid

set @root = hierarchyid::GetRoot()
insert into OrgChart(Position, Title) values (@root, 'CEO')

set @id = @root.GetDescendant(null, null)
insert into OrgChart(Position, Title) values (@id, 'Purchase Manager')

set @id = @root.GetDescendant(@id, null)
insert into OrgChart(Position, Title) values (@id, 'Sales Manager')

select @id = Position.GetDescendant(null, null) from OrgChart where Title = 'Purchase Manager'    
insert into OrgChart(Position, Title) values (@id, 'Purchase Executive')

select @id = Position.GetDescendant(null, null) from OrgChart where Title = 'Sales Manager'    
insert into OrgChart(Position, Title) values (@id, 'Sales Executive')

-- Added SELECT Position.ToString() AS NodeidPath, * FROM OrgChart (14:55 PM)
SELECT Position.ToString() AS NodeidPath, * FROM OrgChart

-- Try the following per Eric Zhang ---
;WITH Cte AS
(
--Get top parent node
SELECT Position,CAST('/'+Title AS VARCHAR(MAX)) fullpath,Title FROM OrgChart WHERE position.GetLevel()=0
UNION ALL
SELECT o.Position,CAST(c.fullpath+'/'+o.title AS VARCHAR(MAX)),o.title FROM OrgChart o JOIN Cte c 
ON o.Position.GetAncestor(1)=C.Position 
)
SELECT Position.ToString() NodePath,fullpath,title FROM Cte

It worked for me.  I got the following results:

Nodeid Position Position Title   (for my "SELECT Position..ToString() AS NodeidPath, * OrgChart")

/		CEO
/1/	0x58	Purchase Manager
/2/	0x68	Sales Manager
/1/1/	0x5AC0	Purchase Executive
/2/1/	0x6AC0	Sales Executive

NodePath    fullpath     title     (for your ";With Cte AS") 

/	/CEO	CEO
/1/	/CEO/Purchase Manager	Purchase Manager
/2/	/CEO/Sales Manager	Sales Manager
/2/1/	/CEO/Sales Manager/Sales Executive	Sales Executive
/1/1/	/CEO/Purchase Manager/Purchase Executive	Purchase Executive

(Note: I don't know how you did to get your code statements and the column names of your "SELECT Position.ToString() NodePath,fullpath,title FROM Cte" statement with the results together inserted in your last post. Could you please tell me how you did it?  It is very important for me to do that kind of thing in my future posts!!)

Now, I can discuss my Questions and the questions in your response:

Question #1:  I don't know what logic I set for the xxx.yyy, etc - i. e. I don't know what "Postion 0x58  0x68  0x5AC0 0x6AC0" means!!??  Please comment on this matter further.

Question #2:  I understand your answers for my (i), (ii) and (iii) better.  If I want to have NodeLevel printed out, how can I do it?

Please kindly help, advise and respond again,

Many Thanks,

Scott Chang  

Hi Scott,

The cte can be seen as a virtual table, you could query that cte as below, see CTE syntax.

;WITH Cte AS
(
--Get top parent node
SELECT Position,CAST('/'+Title AS VARCHAR(MAX)) fullpath,Title FROM OrgChart WHERE position.GetLevel()=0
UNION ALL
SELECT o.Position,CAST(c.fullpath+'/'+o.title AS VARCHAR(MAX)),o.title FROM OrgChart o JOIN Cte c 
ON o.Position.GetAncestor(1)=C.Position 
)
SELECT * FROM Cte

Regarding the column names, some are alias. Basically you can refere to the below demonstration.

CREATE TABLE TABLE4TEST(studentID INT,name VARCHAR(10))
INSERT INTO TABLE4TEST VALUES(1,'Jack'),(2,'John');
GO

SELECT studentID, name FROM TABLE4TEST

--use alias set the column name eventually present as studentName in the result
SELECT studentID,name AS studentName FROM TABLE4TEST

--AS can be ommitted
SELECT studentID,name studentName FROM TABLE4TEST

--when cte  
;WITH Cte AS
(
SELECT studentID,name AS studentName FROM TABLE4TEST
)
SELECT studentID,studentName FROM Cte
;
--when derived table
SELECT studentID,studentName 
FROM (SELECT studentID,name AS studentName FROM TABLE4TEST) AS T; -- alias can be used to a derived table as well
 
DROP TABLE TABLE4TEST;

Regarding the "0x58","0x68", these are hexdecimal numbers which SQL Server use to indicate hierarchy structure.

For node level, there's a built-in function GetLevel().

SELECT Position,Title,Position.GetLevel() AS LEVEL FROM OrgChart

If you have any question, feel free to let me know