Finding higher level hierarchy in SQL Server 2005

See more:

SQL-Server-2005

SQL-Server

Hi,
Does anybody know that to get higher level hierarchy of giving item in SQL Server 2005.Please help me from this bottleneck.i also added scripts for this case.Thanks in advance.
Monish K M
-------------------------------
Example:
When giving EmpID=6 then need to get the result like this

EmpName
-------------------------------------

President
   Vice President
      CTO 
        Group Project Manager
           Project Manager 1
              Team Leader 1

--------------------------------------Script---------------

CREATE TABLE dbo.Emp
(
 EmpID  int PRIMARY KEY,
 EmpName  varchar(30),
 MgrID  int FOREIGN KEY REFERENCES Emp(EmpID)
)
GO
CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)
GO
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
GO