Recursive function to display hierarchial data ...

One of the sql newsgroup member asked this question:

Guys, I have a table by name "TblRecursive" which has following data

ID, Name, ParentID
1, A, 0
2, B, 1
3, C, 2
4, D, 2
5, E, 1

Using the above data I just want to generate a result as below
A
A\B
A\B\C
A\B\D
A\E

Can you help in writing a query for this?

My Solution:

We can achieve this by calling a "User Defined Function (UDF) recursively". Let me show how to do that with a working example.

--Table creation
Create table tblEmployeeInfo
(
EmpId int primary key,
EmpName varchar(30),
MgrId int
)

--Insert test data into it
Insert into tblEmployeeInfo values(1, 'Director', null)
Go
Insert into tblEmployeeInfo values(2, 'Joint Director', 1)
Go
Insert into tblEmployeeInfo values(3, 'Secretary', 2)
Go
Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3)
Go
Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1)
Go

-- User defined function for your requirement
Create function GetEmpPath ( @pEmpid int ) Returns varchar(8000)
As
Begin
Declare @intMgrid int
Declare @strEname varchar(500)
Declare @strPath varchar(500)

Select @intMgrid = mgrid, @strEname = empname From tblEmployeeInfo where
EmpId = @pEmpid

If (@intMgrid is null)
Begin
Set @strPath = @strEname
End
Else
Set @strPath = dbo.GetEmpPath(@intMgrid) + '\' + @strEname
return @strPath
End
Go

--Test the code
Select dbo.GetEmpPath(empid) as Hierarchy, empname as 'Employee Name' from tblEmployeeInfo