When we need to create the hierarchies through sql server we weep..
But do you know the easiest way?
Thanks to Manuj Behal my freind for the same..
1. Create a schema with the following detail
Parent ID, Child ID, Lineage, Name Depth
Null 1 0000 Hardy 0
1 2 0000\0001 Softy 1
1 3 0000\0001 Brother 1
2 4 0000\0001\0002 Freind 2
This is sorted in to DFS..
How to sort it in to BFS and create a tree?
Now get the result shorting on the Some of LineageID + ChildID
You can easily make the structure in BFS or breadth first search form..
The structure would be when you play with queries like :-
select case when depth = 0 then Name else null and so on..
(To make it dynamic one can create a dynamic query for the same..)
More to be updated...