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),
declare @rowcount int, @lvl int, @delcount int
set nocount on
insert into @Hierarchy(_ID,_Level) values ('A',@lvl)
update @Hierarchy set Path=str(Hierarchy_Id,10,0) + '.', ID_Path=cast(_ID as varchar(10)) + '\'
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
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
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.