Recursive function to display hierarchial data ...
One of the SQL newsgroup member asked the following:
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
UDF 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