employee hierarchy query

[nuonuo]

I have employee_id and parent_id for all employee, how to retrieve the result below with sql server 2008 query, thanks in advance.

employee_level1 employee_level2 employee_level3
supervisor null null
supervisor manager1 null
supervisor manager2 null
supervisor manager1 employee1
supervisor manager1 employee2
supervisor manager2 employee3
supervisor manager2 employee4

I wrote one query, but it return only below

employee_level1 employee_level2 employee_level3
supervisor manager1 employee1
supervisor manager1 employee2
supervisor manager2 employee3
supervisor manager2 employee4

[skhanal]

SQL Server has a statement called Common Table Expression, search books online for CTE and you will see an example of employee hierarchy query.