Reader Level:
ARTICLE

Get Recursive Record from Table in Sql Server

Posted by Manish Dwivedi Articles | SQL Server January 11, 2011
I am going to tell you, how to get recursive data from table in sql server.
  • 0
  • 0
  • 7133

Suppose I have a table with the structure as follows:

Table.gif

I want the hierarchical data from this table. For example, for any ID, I want all the children and sub-children.

We can find out all the children from the Table by using this T-SQL statement.


declare
@Hierarchy table(Hierarchy_Id int identity primary key,
_ID VARCHAR(50) not null,
_Level int, ParentID VARCHAR(50),
Path varchar(2000),
ID_Path varchar(2000))
declare
@rowcount int, @lvl int, @delcount int
set
nocount on
set
@lvl=0
insert
into @Hierarchy(_ID,_Level) values ('A',@lvl)
set
@rowcount=@@ROWCOUNT
update
@Hierarchy set Path=str(Hierarchy_Id,10,0) + '.', ID_Path=cast(_ID as varchar(10)) + '\'
while
@rowcount>0
begin
set @lvl=@lvl+1

insert into @Hierarchy(_ID, _Level, ParentID)
select e.Id, @lvl, t.Hierarchy_Id from TableManish e inner join @Hierarchy t on e.Parent=t._ID and t._Level=@lvl-1

set @rowcount=@@ROWCOUNT


update t1 set t1.Path=t2.Path + str(t1.Hierarchy_Id,10,0)+'.', t1.ID_Path=t2.ID_Path + cast(t1._ID as varchar(10))+'\' from @Hierarchy t1 inner join @Hierarchy t2 on t1.ParentID=t2.Hierarchy_Id where t1._Level=@lvl and t2.ID_Path not like '%' + cast(t1._ID as varchar(10)) + '\%'


delete from @Hierarchy where ID_Path is null


set @delcount=@@ROWCOUNT

set @rowcount=@rowcount-@delcount
end
select
replicate(' | ',_Level)+cast(t._ID as varchar(10)) as Tree_Level,e.ID,_Level AS [Level]from @Hierarchy t inner join TableManish e on t._ID=e.Id order by Path

In this query i have input the 'A', Result will be as follows:

Result1.gif

in the above picture, you can see all the hierarchical data related to 'A'

if you will select 'B' then result would be as follows:

Result2.gif

By this way you can get recursive data.
 

COMMENT USING

Trending up