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.

Next Task in Lesson

Populating a Table with Existing Hierarchical Data