Examples of Hierarchical Data
Example 1: Using CTE with Concatenation
CREATE TABLE hierarchy (
id INT PRIMARY KEY,
name VARCHAR(100),
parent_id INT
);
INSERT INTO hierarchy (id, name, parent_id) VALUES
(1, 'Node 1', NULL),
(2, 'Node 1.1', 1),
(3, 'Node 1.2', 1),
(4, 'Node 1.1.1', 2),
(5, 'Node 1.1.2', 2),
(6, 'Node 1.2.1', 3),
(7, 'Node 1.2.2', 3);
WITH hierarchy_paths AS (
SELECT id, name, CAST(name AS VARCHAR(255)) AS path
FROM hierarchy
WHERE parent_id IS NULL
UNION ALL
SELECT h.id, h.name, CAST(CONCAT(hp.path, ' > ', h.name) AS VARCHAR(255))
FROM hierarchy h
JOIN hierarchy_paths hp ON h.parent_id = hp.id
)
SELECT id, name, path
FROM hierarchy_paths;
Output:
Explanation: The given SQL code creates a table “hierarchy” representing a hierarchical structure. The subsequent CTE, “hierarchy_paths,” recursively traverses the hierarchy, building paths for each node. The output showcases the hierarchical paths for every node in the structure.
For example, Node 1.1.1 is displayed with its unique ID, name, and a path representation indicating its position in the hierarchy, such as “Node 1 > Node 1.1 > Node 1.1.1.” This structured output provides a clear view of the parent-child relationships within the hierarchy, facilitating easy comprehension and analysis of the data structure.
Example: Organizational Chart Hierarchy with Recursive CTE in SQL
-- Create table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
ManagerID INT
);
-- Insert values
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID)
VALUES
(1, 'John', NULL),
(2, 'Alice', 1),
(3, 'Bob', 1),
(4, 'Charlie', 2),
(5, 'David', 3);
WITH RecursiveOrgChart AS (
SELECT EmployeeID, EmployeeName, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, roc.Level + 1
FROM Employees e
INNER JOIN RecursiveOrgChart roc ON e.ManagerID = roc.EmployeeID
)
SELECT EmployeeID, EmployeeName, ManagerID, Level
FROM RecursiveOrgChart
ORDER BY Level, EmployeeID;
Output:
Explanation: This SQL code creates an “Employees” table, inserts hierarchical data, and utilizes a recursive CTE (Common Table Expression) named “RecursiveOrgChart” to generate an organizational chart. The final query retrieves and orders the results, displaying EmployeeID, EmployeeName, ManagerID, and Level for clarity.
Hierarchical Data and How to Query It in SQL?
Hierarchical data is structured like a family tree, where each member (node) is linked to others (children) in a parent-child relationship, forming a hierarchy. It’s ideal for representing corporate reporting structures or organizing tasks within projects. Nodes have one parent but parents can have multiple children, with the top-level parent called the ‘root node.’
Despite its complexity and the need to scan the entire tree for searches, modern databases employ clever techniques for efficient hierarchical data retrieval. Although less popular now, it finds utility in storing staffing information. Eric S Raymond once noted its historical challenges, yet its adaptability endures for specific use cases.
Contact Us