Satish Jha (5/24/2006)
Thanks for this article. I have one question here -how can I sort the result from CTE in hierarchical order as well as siblings on some orther order say all records of one level by firstname. In the article the example only sorts it ib hierarchical order
I have used recursion before using while loop and found a tree code very useful. The tree code allows you to sort the data as if it was displayed on a hierarchical tree view. I used another new CTE function to return incremental identifiers for a portion of data (ie, incremental identifiers for the child row of a certain parent) and staggered them in varchar of length 40 (allowing 20 levels with a max of 99 children).
The formatting was a little tricky. I used the RIGHT and LEFT Functions to pad the numbers with 0s
and I have assumed that you want the ordering of the members of each leave to be by the field DateJoined.
For the anchor member definition I had some similar code for that field...
right('0' + cast(ROW_NUMBER() OVER (Partition BY a.ReportingLevelID ORDER BY a.DateJoined) as varchar) ,2) + REPLICATE('00' ,19)
and for the recursive member definition I used...
left(left(b.TreeCode, (b.TreeLevel+1)*2) + right('0' + cast(ROW_NUMBER() OVER (Partition BY b.TreeLevel ORDER BY a.DateJoined) as varchar) ,2) + REPLICATE('00' ,19),40)