help needed in writing a recursive function

Guys, I have a table with following data
Table Name :TblRecursive
Sample records:
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?
Regards
PradeepLook at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
In SQL 2005 you can use a common table expression with a recursive member.
ML
http://milambda.blogspot.com/|||http://vyaskn.tripod.com/hierarchie...r_databases.htm
Madhivanan|||Thanks for your response ML.
As I told earlier .. I want to generate the string as A\B and so on. But
your UDF gives me 3 columns.
Also I don't want to provide any input to the function also. I want it to
just start from the beginning and go on till the end. Any idea on how to
achieve this?
Regards
Pradeep
"ML" wrote:

> Look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> In SQL 2005 you can use a common table expression with a recursive member.
>
> ML
> --
> http://milambda.blogspot.com/|||Concatenating values into a single string really should be done on the
cilent, as it has no relational value - it's pure presentation.
There are a few ways to do it on the server if absolutely necesary:
declare @.result varchar(8000)
set @.result = N''
select @.result = <column> + '/' + @.result
from <function>
order by <level?>
ML
http://milambda.blogspot.com/|||Check whether this sample helps you!
--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
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"SqlBeginner" wrote:
> Thanks for your response ML.
> As I told earlier .. I want to generate the string as A\B and so on. But
> your UDF gives me 3 columns.
> Also I don't want to provide any input to the function also. I want it to
> just start from the beginning and go on till the end. Any idea on how to
> achieve this?
> Regards
> Pradeep
>
> "ML" wrote:
>|||This is what I was looking for. Thanks a lot Vadivel. It really helped me.
Regards
Pradeep
"Vadivel" wrote:
> Check whether this sample helps you!
> --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 wher
e
> 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
> Hope this helps!
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "SqlBeginner" wrote:
>|||Take into acount that in SQL 2000 the maximum recursion level is 32. And
quite frankly, recursion is not necesary here.
In my other post there's a solution that does not use a rexursive function.
Another example can also be found here:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
http://milambda.blogspot.com/