In SQL Server, retrieving organizational hierarchy data often involves querying a table that stores hierarchical relationships. This is commonly achieved using one of several methods, including recursive Common Table Expressions (CTEs), adjacency list models, or nested set models. Here's an overview of how you can approach each method.
1. Adjacency List Model
In this model, you typically have a table where each row includes a reference to its parent row. For example.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);
To find the hierarchy, you can use a recursive CTE. Here’s an example of how to retrieve the hierarchy of employees.
WITH EmployeeHierarchy AS (
-- Anchor member: start with top-level employees (those with no manager)
SELECT
EmployeeID,
Name,
ManagerID,
1 AS Level -- Root level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member: join the hierarchy with itself to get child employees
SELECT
e.EmployeeID,
e.Name,
e.ManagerID,
eh.Level + 1 AS Level
FROM Employees e
INNER JOIN EmployeeHierarchy eh
ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, ManagerID, EmployeeID;
2. Nested Set Model
In this model, you store hierarchical data using left and right values that define the position of nodes in the hierarchy. Here’s an example table.
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY,
CategoryName NVARCHAR(100),
LeftValue INT,
RightValue INT
);
To retrieve the hierarchy, you would perform a self-join.
SELECT
parent.CategoryName AS ParentCategory,
child.CategoryName AS ChildCategory
FROM Categories parent
INNER JOIN Categories child
ON child.LeftValue BETWEEN parent.LeftValue AND parent.RightValue
WHERE parent.LeftValue < child.LeftValue
ORDER BY parent.LeftValue, child.LeftValue;
3. Path Enumeration Model
In this model, each row stores the path to its root. For example.
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY,
CategoryName NVARCHAR(100),
Path NVARCHAR(MAX)
);
To get the hierarchy, you can query the Path field. Here’s a simple example of getting all descendants of a given node.
DECLARE @CategoryID INT = 1; -- Assuming the root node has CategoryID 1
SELECT *
FROM Categories
WHERE Path LIKE (SELECT Path FROM Categories WHERE CategoryID = @CategoryID) + '%';
Summary
- Adjacency List Model: Uses a ManagerID column to establish parent-child relationships. Recursive CTEs are commonly used to traverse the hierarchy.
- Nested Set Model: Uses LeftValue and RightValue columns to represent hierarchical relationships. Efficient for read-heavy operations.
- Path Enumeration Model: Stores the path to the root, making it easy to query descendants and ancestors.
The choice of model depends on your specific needs and the nature of your hierarchical data.