sql sql-server sql-server-2008-r2 common-table-expression recursive-query

Dump list of full paths of parent/child type records in SQL Server 2008 R2

I need to retrieve the list of full paths of groups (folders) stored in the database as such:

select * from groups;

group_id    parent_group_id    name
--------    ---------------    -------------------------------
1           NULL               Root
2           1                  Folder1
3           2                  Folder2
4           3                  Folder3

There is no limit to the possible depth of groups (could be nested almost indefinitely), so I don't know in advance how many levels I could have.

I would like to be able to get the following results from a query and load that into a table that I could use to join full paths with group_ids in other queries:

group_id   path
--------   --------------------------------------------
1          /Root
2          /Root/Folder1
3          /Root/Folder1/Folder2
4          /Root/Folder1/Folder2/Folder3

I've been looking at some other examples with similar needs, but it never seems to fit perfectly to what I want, and without a clear example, I'm having trouble to do it alone.

The only thing I could think of was to join aliases of the same table up to the max number of levels, which could be a lot, and that is not practical. I would need a way to loop recursively somehow for each group and go up the tree, appending the path to a variable until I'm at the end, and then store the deepest child ID and its full path in a table.

Performance is also very important. This is a production database and I can't afford slowdowns, which is why I want to dump it only when I need it and then reuse static data. Ultimately I want to dump thousands of records each linking to a group_id and print their full path.

Without giving me the whole thing for free, could someone point me in the right direction and maybe give me a generic example to copy from?

We are using SQL Server 2008 R2.

Solution

Sounds like something that using a recursive CTE might be able to solve. A CTE (or common table expression) will allow you to create a table-like structure without having to create a table or view. CTEs also allow you to create recursive queries, which in your case can help tremendously. The structure allows you to join the CTE onto itself, lending to a 'cascading' type of result as you're intending to achieve here.

For example, you could do something like this:

WITH grouppaths (group_id, group_path) AS
(
    SELECT group_id, pathname
    FROM GroupPath
    WHERE parent_group_id IS NULL

    UNION ALL

    SELECT gp.group_id, gps.group_path + '/' + gp.pathname
    FROM GroupPath gp
    JOIN grouppaths gps ON gps.group_id = gp.parent_group_id
)

SELECT 
  group_id, group_path
FROM
  grouppaths

You can check this out here using SqlFiddle.

Microsoft provides information and samples about using CTEs here: http://msdn.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx

More specifically, this link provides further information about recursive CTEs: http://msdn.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx