• Article

Setting up a Table with Hierarchical Data

The first step in converting a table to a hierarchical structure is to create the table to convert. The following procedure creates a table that contains information on an employee's ID, manager ID, and login ID.

To create the EmployeeDemo table

  • In a Query Editor window, run the following code to create a new table named EmployeeDemo.

    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

  • This new EmployeeDemo table represents a typical table in an existing database that you might want to migrate to a new structure. In a Query Editor window, run the following code to show how the table uses a self join to display the employee/manager relationships:

    SELECT 
     Mgr.EmployeeID AS MgrID, Mgr.LoginID AS Manager, 
     Emp.EmployeeID AS E_ID, Emp.LoginID AS LoginID 
FROM EmployeeDemo AS Emp
LEFT JOIN EmployeeDemo AS Mgr
ON Emp.ManagerID = Mgr.EmployeeID
ORDER BY MgrID, E_ID;

    Here is the result set.

    MgrID Manager E_ID LoginID

    NULL NULL 1 zarifin

    1 zarifin 2 tplate

    1 zarifin 3 hjensen

    2 tplate 4 schai

    2 tplate 5 elang

    2 tplate 6 gsmits

    3 hjensen 7 sdavis

    3 hjensen 8 norint

    4 schai 9 jwang

    4 schai 10 malexander

In the next task, we will create a new table with a hierarchyid data type, and move the data into the new table.