Monday, April 26, 2010

SQL Server and the benefits of Char based sorting.. Trees and Hierarchies in SQL Server

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..)

Hardy
           Softy
                    freind
           Brother

More to be updated...

No comments:

Recent Posts