Suppose I have a table with the structure as follows:
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:
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:
By this way you can get recursive data.