Vadivel's blog

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 ...