Loading ...

How to show hierarchies of childs by using SQL Serve | CodeAsp.Net

How to show hierarchies of childs by using SQL Serve

 /5
0 (0votes)

First I create a table

Create Table ParentChild_Table
(ID int identity(100,1),[Name]varchar(100),ParentID int)

Then insert some records

INSERT INTO ParentChild_Table VALUES('JOHN',0)
INSERT INTO ParentChild_Table VALUES ('RINA',100)
INSERT INTO ParentChild_Table VALUES ('RAJU',100)
INSERT INTO ParentChild_Table VALUES ('RAJEEV',101)
INSERT INTO ParentChild_Table VALUES ('RAVI',103)
INSERT INTO ParentChild_Table VALUES ('ALBERT',102)
INSERT INTO ParentChild_Table VALUES ('NITIN',104)
INSERT INTO ParentChild_Table VALUES ('ALBERT',104)
INSERT INTO ParentChild_Table VALUES ('A1',0)
INSERT INTO ParentChild_Table VALUES ('A1.1',108)
INSERT INTO ParentChild_Table VALUES ('A1.2',108)
INSERT INTO ParentChild_Table VALUES ('A1.1.1',109)

on executing the query "SELECT * FROM ParentChild_Table"

the below records are found
ID          Name                                                                                                 ParentID
----------- ---------------------------------------------------------------------------------------------------- -----------
100         JOHN                                                                                                 0
101         RINA                                                                                                 100
102         RAJU                                                                                                 100
103         RAJEEV                                                                                               101
104         RAVI                                                                                                 103
105         ALBERT                                                                                               102
106         NITIN                                                                                                104
107         ALBERT                                                                                               104
108         A1                                                                                                   0
109         A1.1                                                                                                 108
110         A1.2                                                                                                 108
111         A1.1.1                                                                                               109

(12 row(s) affected)
to getting all child hierarchies  i write down below stored procedure

 

Create Procedure GettingChildhierarchy
@ID int
as
Set Nocount on
-- table #Temp1 store all the PID  to be returned         
create table #Temp1(PID int)         
--table #Temp2 contains PID in the previous iteration         
create table #Temp2(PID int)         
--table #Temp3 is a temporary table used to store intermediate results         
create table #Temp3(PID int)

insert into #Temp2(PID )         
select ID from [ParentChild_Table]  where ParentID= @ID        
union          
select ID  from [ParentChild_Table]  where ID= @ID        

while exists(select * from #Temp2)         
begin         
insert into #Temp1(PID)         
select #Temp2.PID from #Temp2         

insert into #Temp3(PID)         
select ID  from [ParentChild_Table], #Temp2  where #Temp2.PID=[ParentChild_Table].[ParentID]         

delete from #Temp2         
insert into #Temp2         
select * from #Temp3         
delete from #Temp3          
end

---End       
-------Start sava distinct PIDId in another temp table       
select * from ParentChild_Table where ID in( select distinct* from #Temp1)
drop table #Temp1 
drop table #Temp2
drop table #Temp3  
-------end
Go  

on executing the procedure  as " exec GettingChildhierarchy 100 "

ID          Name                                                                                                 ParentID
----------- ---------------------------------------------------------------------------------------------------- -----------
100         JOHN                                                                                                 0
101         RINA                                                                                                 100
102         RAJU                                                                                                 100
103         RAJEEV                                                                                               101
104         RAVI                                                                                                 103
105         ALBERT                                                                                               102
106         NITIN                                                                                                104
107         ALBERT                                                                                               104


and on "Exec GettingChildhierarchy 108"

ID          Name                                                                                                 ParentID
----------- ---------------------------------------------------------------------------------------------------- -----------
108         A1                                                                                                   0
109         A1.1                                                                                                 108
110         A1.2                                                                                                 108
111         A1.1.1                                                                                               109

 

 

Comments (1)

Top Posts