Thanks a lot.
You saved my time. Now I'll restructure the database to get the desired result.
Thank you once again for your valuable time and advice.
Regards
Prashant
>MS SQL Server 2000 does not support hierarchical queries. The hierarchical queries support has been added in MS SQL Server 2005
>
>>Please can anyone suggest me a counter query in MS SQL for the one in Oracle.
>>
>>In Oracle I use the following Query to traverse the data from root to the last child.
>>
>>

>>SELECT LPAD(' ',3*LVLCODE) || Name,
>>         LVLCODE, PRCODE
>> FROM   LEVELMST
>> CONNECT BY PRIOR LVLCODE = PRCODE
>> START WITH PRCODE = 0
>>

>>
>>To traverse from child to root the query in Oracle is
>>
>>

>>SELECT LPAD(' ',3*LVLCODE) || Name,
>>         LVLCODE, PRCODE
>> FROM   LEVELMST
>> CONNECT BY LVLCODE = PRIOR PRCODE
>> START WITH LVLCODE = 17
>>

>>
>>Please help me with these queries.
>>Thanks in advance.