i am searching just bb

emp_id 8 and 13 has the value bb

the parentid of bb(8) is 7

and parentid of bbbb (7) is 6

and parentid of bbbbb(6)...

Sorry for not creating the script.

This is what i am looking for..

For example, if user searches bb , the user expects following op. the reason is its hierarchical

so if its...

---- create table

create table test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))

---- insert records

insert into test values (1, '11', 0, 'a')

insert into test values (2, '111', 1, 'b')

insert into...

Kindly find attached the table and the op required..

Hope this info is good enough...

Thanks

Peter

this is what i tried and not getting the expected op

;WITH SInfo AS

(

SELECTsId

,scode

,ParentId

,sName

,CONVERT(nvarchar(800), scode) AS Hierarchy

FROMtest

WHEREParentId=0

UNIONALL

SELECTTH.sId

,TH.scode

,TH.ParentId

,TH.sName

,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))

FROMtest TH

INNER JOINSInfoONSInfo.sId = TH.ParentId

)

Select * from...

Hello there,

I tried different options but not getting exactly what i am looking for..

Kindly refer the attachment for the expected op

Thanks

Peter