A standard recursive call would involve a cursor but that is a dirty word on this forum.

Depending on the target application you could use a 'fragment caching' technique. Fragment caching is saving a frequently used query to the application cache, for example standard navigation.

But without knowing your target I can't say.

Another technique you could use would be to build a summary table (very old technique that is probably outlawed by the SQL police) that would contain something like

o parent node

o node

o node name

o indent

You could also add a trigger that rebuilt this table when your node list got changed.

<disclaimer>

As I said these are techniques that should only be used if the circumstances are right.

</disclaimer>