To create the EmployeeDemo table
CREATE TABLE EmployeeDemo (EmployeeID int, LoginID varchar(200), ManagerID int);
INSERT INTO EmployeeDemo
VALUES (1, 'zarifin', Null),
(2, 'tplate', 1),
(3, 'hjensen', 1),
(4, 'schai', 2),
(5, 'elang', 2),
(6, 'gsmits', 2),
(7, 'sdavis', 3),
(8, 'norint', 3),
(9, 'jwang', 4),
(10, 'malexander', 4);
To examine the structure and data of the EmployeeDemo table
select * from EmployeeDemo
SELECT
Mgr.EmployeeID AS MgrID, Mgr.LoginID AS Manager,
Emp.EmployeeID AS E_ID, Emp.LoginID
FROM EmployeeDemo AS Emp
LEFT JOIN EmployeeDemo AS Mgr
ON Emp.ManagerID = Mgr.EmployeeID
ORDER BY MgrID, E_ID;
To create a new table named NewOrg
CREATE TABLE NewOrg
(
OrgNode hierarchyid,
EmployeeID int,
LoginID nvarchar(50),
ManagerID int
CONSTRAINT PK_NewOrg_OrgNode
PRIMARY KEY CLUSTERED (OrgNode)
);
GO
To create a temporary table named #Children
CREATE TABLE #Children
(
EmployeeID int,
ManagerID int,
Num int
);
GO
CREATE CLUSTERED INDEX tmpind ON #Children(ManagerID, EmployeeID);
GO
To populate the NewOrg table
INSERT #Children (EmployeeID, ManagerID, Num)
SELECT EmployeeID, ManagerID,
ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID)
FROM EmployeeDemo
GO
SELECT * FROM #Children ORDER BY ManagerID, Num
Populate the NewOrg table. Use the GetRoot and ToString methods to concatenate the Num values into the hierarchyid format, and then update the OrgNode column with the resultant hierarchical values:
WITH paths(path, EmployeeID)
AS (
-- This section provides the value for the root of the hierarchy
SELECT hierarchyid::GetRoot() AS OrgNode, EmployeeID
FROM #Children AS C
WHERE ManagerID IS NULL
UNION ALL
-- This section provides values for all nodes except the root
SELECT
CAST(p.path.ToString() + CAST(C.Num AS varchar(30)) + '/' AS hierarchyid),
C.EmployeeID
FROM #Children AS C
JOIN paths AS p
ON C.ManagerID = P.EmployeeID
)
INSERT NewOrg (OrgNode, O.EmployeeID, O.LoginID, O.ManagerID)
SELECT P.path, O.EmployeeID, O.LoginID, O.ManagerID
FROM EmployeeDemo AS O
JOIN Paths AS P
ON O.EmployeeID = P.EmployeeID
GO
select * from EmployeeDemo
SELECT OrgNode.ToString() AS LogicalNode, *
FROM NewOrg
ORDER BY LogicalNode;
To index the NewOrg table for efficient searches
ALTER TABLE NewOrg
ADD H_level AS OrgNode.GetLevel() ;
CREATE UNIQUE INDEX EmpBFInd
ON NewOrg(H_level, OrgNode) ;
CREATE UNIQUE INDEX EmpIDs_unq ON NewOrg(EmployeeID) ;
SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID
FROM NewOrg
ORDER BY OrgNode;
SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID
FROM NewOrg
ORDER BY H_Level, OrgNode;
SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID
FROM NewOrg
ORDER BY EmployeeID;
GO