I wanted a simple example of a hierarchical query using a common table expression in SQL Server. MSDN had an example called Recursive Queries Using Common Table Expressions, but it relied on the Adventure Works database and was joining across three different tables. Then I stumbled upon another blog called Working with hierarchical data in SQL Server databases that had all of the queries to make the tables and populate the data, but it was using stored procedures. So I took the liberty of putting them together into a very simple example that you can run:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Emp]’) AND type in (N’U’))
BEGIN
— Create the table
CREATE TABLE dbo.Emp
(
EmpID int PRIMARY KEY,
EmpName varchar(30),
MgrID int FOREIGN KEY REFERENCES Emp(EmpID)
)
— Create a non-clustered index for query performance
CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)
— Populate the table with data
INSERT dbo.Emp SELECT 1, ‘President’, NULL
INSERT dbo.Emp SELECT 2, ‘Vice President’, 1
INSERT dbo.Emp SELECT 3, ‘CEO’, 2
INSERT dbo.Emp SELECT 4, ‘CTO’, 2
INSERT dbo.Emp SELECT 5, ‘Group Project Manager’, 4
INSERT dbo.Emp SELECT 6, ‘Project Manager 1’, 5
INSERT dbo.Emp SELECT 7, ‘Project Manager 2’, 5
INSERT dbo.Emp SELECT 8, ‘Team Leader 1’, 6
INSERT dbo.Emp SELECT 9, ‘Software Engineer 1’, 8
INSERT dbo.Emp SELECT 10, ‘Software Engineer 2’, 8
INSERT dbo.Emp SELECT 11, ‘Test Lead 1’, 6
INSERT dbo.Emp SELECT 12, ‘Tester 1’, 11
INSERT dbo.Emp SELECT 13, ‘Tester 2’, 11
INSERT dbo.Emp SELECT 14, ‘Team Leader 2’, 7
INSERT dbo.Emp SELECT 15, ‘Software Engineer 3’, 14
INSERT dbo.Emp SELECT 16, ‘Software Engineer 4’, 14
INSERT dbo.Emp SELECT 17, ‘Test Lead 2’, 7
INSERT dbo.Emp SELECT 18, ‘Tester 3’, 17
INSERT dbo.Emp SELECT 19, ‘Tester 4’, 17
INSERT dbo.Emp SELECT 20, ‘Tester 5’, 17
END
GO
–Hierarchical Query using Common Table Expressions
WITH ReportingTree (EmpID, EmpName, MgrID, Lvl)
AS
(
–Anchor Member
SELECT *, 0 as Lvl FROM emp WHERE MgrId IS NULL
UNION ALL
–Recusive Member
SELECT emp.EmpID, emp.EmpName, emp.MgrId, ReportingTree.Lvl+1
FROM emp INNER JOIN ReportingTree
on emp.MgrID = ReportingTree.EmpID
)
SELECT * FROM ReportingTree
You will, however, need to refer to the MSDN documentation link to see how it’s working.
Load comments