Folder hierarchy from a database table

[Do]

Hi,

I have a database table with the following fields: id, name, parentid.
These fields are supposed to create a hierarchy for a list box, an infinite
hierarchy
Child fields of parent fields should be indented.

Root folders have a parentid of zero. Child folders in the hierarchy have
parentids that correspond
to ids. Here are some sample records of folder:

id name parentid
0 Microsoft 0
1 ABC 0
2 Oracle 1
3 Sony 1
4 AOL 3

How do I write a function to loop through this and get me the correct
hierarchy...
I'm at a loss. I loop through a data reader WHERE the parentid = 0
to start. After that, how to I loop as far as I need to return everything
in a hierarchy
something like this

------------------

 

[Dmitry Andreev]

Check this article
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/
html/sql03b8.asp


Sadly, if you use SQL 2000 or msde, where no "tree" functionality like
CONNECT BY, LEVELS...
in TSQL.

 

[Konrad Rotuski]

maybe try adding some column to the table, like PATH, by
examininh which you could easily tell where in hierarchy
specific element is. Another option is stored procedure
which loops through all elements using cursor, and returns
dataset with whatever you want <- i implemented this some
time ago, it was quite different functionality, but when
it comes to trees in ms sql, it should be easy to
customize to your problem