Create a hierarchical identifier

Question

I would like to insert a hierarchy like this

/ - CEO (Root)

/ 1 / - Purchasing Manager / 2 / - Sales Manager

/ 1/1 / - Executive Director / 2/1 / - Sales Manager

This is what I would like to use in the hierarchy, is it correct, if so, how can I do this, can someone give me a piece of code.

Answer 1

I ran into this question looking for information about the hierarchyid data type, and thought it would be interesting if someone else came after me to see the hierarchyid insertion code according to the question.

I do not claim that these are the only ways to insert hierarchyid s, but I hope this helps those who, like me, do not have experience with this data type.

Using this table,

 create table OrgChart ( Position hierarchyid, Title nvarchar(50) ) 

you can use Parse to directly insert hierarchyid using string paths:

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

and use the following query to check the table

 select Position.ToString(), * from OrgChart 

You can also use hierarchyid GetRoot and GetDescendant data type methods to create a hierarchy. I found this method more cumbersome, but I believe that using these methods is necessary if you programmatically manage the hierarchy.

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

Definitely check the links provided in another answer, but hopefully this code to try also helps.

Answer 2

Suppose you have a self-join table schema (as shown below) and that your CEOID ManagerID is NULL.

 CREATE TABLE Employees ( EmployeeID int NOT NULL IDENTITY(1,1) PRIMARY KEY , JobTitle nvarchar(50) NOT NULL , FirstName nvarchar(50) NOT NULL , LastName nvarchar(50) , ManagerID int ) ALTER TABLE dbo.Employee ADD CONSTRAINT FK_Employee_ManagingEmployee FOREIGN KEY (MangerID) REFERENCES dbo.Employee (EmployeeID) ON UPDATE NO ACTION ON DELETE NO ACTION 

You can automatically generate an initial set of hierarchy values ​​using the following recursive CTE:

 ;WITH EmployeeHierarchy ( EmployeeHierarchyID , EmployeeID , JobTitle , LastName , FirstName , ManagerID ) AS ( SELECT HIERARCHYID::GetRoot() AS EmployeeHierarchyID , EmployeeID , JobTitle , LastName , FirstName , ManagerID FROM Employee WHERE ManagerID IS NULL UNION ALL SELECT HIERARCHYID::Parse(EmployeeHierarchyID.ToString() + ( CONVERT(VARCHAR(20), ROW_NUMBER() OVER ( ORDER BY EmployeeHierarchy.EmployeeID )) ) + '/') AS EmployeeHierarchy , EmployeeHierarchy.EmployeeID , EmployeeHierarchy.JobTitle , EmployeeHierarchy.LastName , EmployeeHierarchy.FirstName , EmployeeHierarchy.ManagerID FROM Employee INNER JOIN EmployeeHierarchy AS d ON Employee.ManagerID = d.EmployeeID ) SELECT TOP (100) PERCENT EmployeeHierarchyID , EmployeeID , JobTitle , LastName , FirstName , ManagerID INTO #EmployeeHierarchy FROM EmployeeHierarchy ORDER BY EmployeeHierarchyID 

Then it becomes a rather trivial matter to add a hierarchy column to the table, add an index to it and then fill it by joining the temp table.

 UPDATE Employee SET EmployeeHierarchyID = #EmployeeHierarchy.EmployeeHierarchyID FROM Employee INNER JOIN #EmployeeHierachy ON Employee.EmployeeID = #EmployeeHierarchy.EmployeeID 

However, keep in mind that if you want these hierarchies to remain consistent after they are added, there are very specific ways to save them.