Hierarchical queries with Common Table Expressions
time to read 4 min | 762 words

[Via Mitch Denny's Blog ] [Via Sql Down Under]

It's usualy hard to efficently get hierarchical data using SQL. But apperantely SQL Server can do that easily. The key to this is to create common table expression that recursively points to itself, like this:

WITH AllEmployeesOf([EmployeeId], [Name], [ManagerName])

AS

(

      SELECT m.[EmployeeId], m.[Name], convert(nvarchar(50),null) as ManagerName

            FROM [Employees] m WHERE @employee_id = m.[EmployeeId]

      UNION ALL

      SELECT e.[EmployeeId], e.[Name], AllEmployeesOf.[Name] as ManagerName

            FROM [Employees] e JOIN AllEmployeesOf ON

      e.[ManagerId] = AllEmployeesOf.[EmployeeId]

)

SELECT * FROM AllEmployeesOf

Before this I would resort to either getting all of the rows or issuing several queries. Nice.

Tweet Share Share 0 comments
Tags: