Can I generate a recursive query in SQL?

See more:

SQL-Server-2005

SQL

SQL-Server

For example:

I have a table in which I maintain all employees and the employees they report to. Now, If I want to know who is report to whom based on a single employees, how can I achieve that list of all employees recursively.

Table Structure is TABLE : (EmpId, ReportTo)

Employee 1 is report to none, ReportTo = 0
Employee 2 is Reporting to Employee 1, ReportTo = 1
Employee 3 is Reporting to Employee 1, ReportTo = 1

Employee 4 is Reporting to Employee 2, ReportTo = 2
Employee 5 is Reporting to Employee 2, ReportTo = 2
Employee 6 is Reporting to Employee 4, ReportTo = 4
Employee 7 is Reporting to Employee 5, ReportTo = 5

Now If I want to know recursively the chain of all the subordinates of
Employee 1. How would that be possible by query?

Any help would be highly thankful...


Regards,

Jawwad!