[sql-server] Simulation of CONNECT BY PRIOR of Oracle in SQL Server

How can I get the functionality of CONNECT BY PRIOR of Oracle in SQL Server 2000/2005/2008?

@Alex Martelli's answer is great! But it work only for one element at time (WHERE name = 'Joan') If you take out the WHERE clause, the query will return all the root rows together...

I changed a little bit for my situation, so it can show the entire tree for a table.

table definition:

CREATE TABLE [dbo].[mar_categories] ( 
    [category]  int IDENTITY(1,1) NOT NULL,
    [name]      varchar(50) NOT NULL,
    [level]     int NOT NULL,
    [action]    int NOT NULL,
    [parent]    int NULL,
    CONSTRAINT [XPK_mar_categories] PRIMARY KEY([category])
)

(level is literally the level of a category 0: root, 1: first level after root, ...)

and the query:

WITH n(category, name, level, parent, concatenador) AS 
(
    SELECT category, name, level, parent, '('+CONVERT(VARCHAR (MAX), category)+' - '+CONVERT(VARCHAR (MAX), level)+')' as concatenador
    FROM mar_categories
    WHERE parent is null
        UNION ALL
    SELECT m.category, m.name, m.level, m.parent, n.concatenador+' * ('+CONVERT (VARCHAR (MAX), case when ISNULL(m.parent, 0) = 0 then 0 else m.category END)+' - '+CONVERT(VARCHAR (MAX), m.level)+')' as concatenador
    FROM mar_categories as m, n
    WHERE n.category = m.parent
)
SELECT distinct * FROM n ORDER BY concatenador asc

(You don't need to concatenate the level field, I did just to make more readable)

the answer for this query should be something like:

I hope it helps someone!

now, I'm wondering how to do this on MySQL... ^^

I haven't used connect by prior, but a quick search shows it's used for tree structures. In SQL Server, you use common table expressions to get similar functionality.