SQL: Recursive Path

Is it possible to create a "tree resolver" in SQL?

I have a table:

ID Name Parent
1  a
2  b    1
3  c    1
4  d    3

Now I want a SQL query that returns:

ID   PATH
1    /a
2    /a/b
3    /a/c
4    /a/c/d

Is t开发者_Python百科his possible with SQL? It would make many things easier for me. Any help would really be appreciated!

Using CTE in sql server 2005 and later, here's a snippet that I have to do this:

WITH Paths([Level], [FullPath], [ID]) AS 
(
    SELECT 
        0 AS [Level], 
        Name AS FullPath, 
        ID
    FROM dbo.Entity
    WHERE (ParentEntityID IS NULL)

    UNION ALL

    SELECT 
        p.[Level] + 1 AS [Level], 
        CASE RIGHT(p.[FullPath], 1) 
        WHEN '\' THEN p.[FullPath] + c.[Name] 
        ELSE p.[FullPath] + '\' + c.[Name] 
    END AS FullPath, 
    c.ID
    FROM dbo.Entity AS c 
    INNER JOIN Paths AS p ON p.ID = c.ParentEntityID
)
SELECT [FullPath], [ID]
FROM Paths

Depending on what database server use, this functionality may be provided for you already. Otherwise you can create a function that call itself to return this information, or implement a Materialized Path solution.

Update:

For DB2 you can make use of Recursive Common Table Expressions.

Yes it is, look here. You can use the "start with" and "connect by prior" statements, I've used this in the past to create breadcrumbs in a web app.

There are several different ways to represent a tree in an SQL database. I guess I don't know much, but I do know that Django Treebeard uses 3 different ways to do it. If you look at the documentation, it has short descriptions of each way:

adjacency list -- what you're doing already

materialized path -- article: http://www.dba-oracle.com/t_sql_patterns_trees.htm

nested sets -- oh, here's wikipedia: http://en.wikipedia.org/wiki/Nested_set_model

Suppose we have a simple table called DLFolder with the following columns:

| folderId | name | parentFolderId |

In Oracle you can use the sys_connect_by_path operation.

select fo.folderId as folder_id, sys_connect_by_path(fo.name, '/') as relname
from DLFolder fo
start with fo.parentFolderId=0
connect by prior fo.folderId = fo.parentFolderId

Will give the following result:

/1020_Training_Material
/1020_Training_Material/2000_IBBA
/1020_Training_Material/2000_IBBA/5000_FR
/1020_Training_Material/2000_IBBA/5050_NL

See http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions164.htm