its urgent

[bcss03a006]

I have a table with two columns 1- id 2- parent_id I want a SQL query that take parent_id as perameter and retrieve all its children and children of children and so on upto N level. Data in table like: ID parent_id S1 SG G1 FG GP01 FG GP0101 GP01 G11 G1 G12 G1 G111 G11 G112 G11 G113 G11 If I pass FG as parent_id, the query should return G1,GP01,GP0101,G11,G12,G111,G112,G113. Please help me in this regard

[Niladri_Biswas]

This kind of problem can be easily solved with Recursive CTE's. Try this. declare @tbl table(id varchar(20),parentid varchar(20)) insert into @tbl select 'S1','SG' union all select 'G1','FG' union all select 'GP01','FG' union all select 'GP0101','GP01' union all select 'G11','G1' union all select 'G12','G1' union all select 'G111','G11' union all select 'G112','G11' union all select 'G113','G11'

;with cte as
(
select t1.parentid,t1.id,0 AS [Level] from @tbl t1
where t1.parentid = 'FG'
union all
select t1.parentid,t1.id,[Level]+1 from @tbl t1
inner join cte c
on c.id = t1.parentid
)
select left(Decendants,len(Decendants)-1) Decandants
from
(
select id + ','
from cte
for xml path ('')
) Result(Decendants)

Pass the parent id as a parameter from ur stored proc. O/P:

Decandants
G1,GP01,GP0101,G11,G12,G111,G112,G113

:)

Niladri Biswas

modified on Friday, October 30, 2009 6:11 AM

[Mycroft Holmes]

Niladri_Biswas wrote:

Note- I sql server 2008, for solving this kind of problem you can take the advantage of Hierarchial Id's

Can 2008 use this format as a hierarchyID, there us no delimiter for the levels, the standard HID.ToString() looks like '/G12/1/'