View Single Post
  #2 (permalink)  
Old June 11th, 2008, 06:36 PM
Old Pedant Old Pedant is offline
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, actually the right answer is to *STORE* your data in the form you are showing, or nearly so.

I do it like this:
Code:
ID  ParentID  Path             Name
1   0         0001             Root1
2   0         0002             Root2
3   1         0001-0003        Root1_Child1
4   1         0001-0004        Root1_Child2
5   2         0002-0005        Root2_Child1
6   3         0001-0003-0006   Root1_Child1_SubChild1
7   3         0001-0003-0007   Root1_Child1_SubChild2
That is, PATH is a VARCHAR that uses "padded" ids of fixed length. And so now, if you do an ORDER BY PATH you get

Code:
ID  ParentID  Path             Name
1   0         0001             Root1
3   1         0001-0003        Root1_Child1
4   1         0001-0004        Root1_Child2
6   3         0001-0003-0006   Root1_Child1_SubChild1
7   3         0001-0003-0007   Root1_Child1_SubChild2
2   0         0002             Root2
5   2         0002-0005        Root2_Child1
Ideal for building "tree" structures. And building the PATH field as you create the table is going to get you the best possible performance, later.

But starting from the other end is tougher...

SQL Server doesn't do well with recursion, though SQL Server 2005 has some new support for it that I haven't tried yet.

If you *KNOW* the maximum "depth" of your tree, you could clearly build this in a single query using a UNION and a SELECT for each "depth".

Otherwise, I think you'd need to use a Stored Procedure, cursors, a temporary table, etc.