SQL script for menu structure

I want to create menu str开发者_开发技巧ucture from a database.

For example, the database is the following:

id   name     parent

1     x         null

2     y         1

3     z         null

4     a         3

5     b         2

The result should be:

-x

--y

---b

-z

--a

Now, the algorithm I have on mind is the following: 1. first find a column which has 'parent' = null. (I'll call this column A) 2. then find a column which has 'parent' = A's parent. (I'll call this column B) 3. then find a column which has 'parent' = B's parent. etc.

WHILE (SELECT MenuText FROM dbo.Table2 WHERE parent = NULL)
BEGIN
   SELECT MenuText FROM dbo.Table2 WHERE parent = ...
END

Is it good?

---

Looks like a candidate for a tree query (I don't know whether the Express Edition supports this, though).