SQL query to build folder tree

Posted on 2012-08-31
Last Modified: 2012-08-31
this is regarding a SQL based document management system, all "folders" are simply rows in a single table, each folder having an ID and a ParentID.

I need a query that will list all folders whose ParentID = X, but then on down the tree until there are no more results.

example tree:

Base folder
             last year
             this year

example data:

foldername    ID        parentID

base folder      1            NULL
items               2            1
old                   6             2
red                   7             2
details              3             1
last year           8            3
this year           9            3

seems like something that could be done with a cursor, but I cant get my head around it. keep in mind the folder trees can be of any configuration or have several levels, I can't simply rely on them only have a set number of levels.
Question by:RustyZ32
    LVL 21

    Accepted Solution

    LVL 21

    Expert Comment

    ";WITH FamilyTreeAS(    SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 0 AS Generation    FROM @TT    WHERE ParentID IS NULL        UNION ALL        SELECT Fam.*,FamilyTree.Name AS ParentName, Generation + 1    FROM @TT AS Fam    INNER JOIN FamilyTree    ON Fam.ParentID = FamilyTree.ID)SELECT * FROM FamilyTree
    LVL 6

    Author Comment

    that works but it only goes down 1 level, in some cases the folder trees can be several levels deep.

    how can tweak that to make it continue running  until it finds no more subfolders?
    LVL 6

    Author Closing Comment

    Nevermind about folder levels comment, my test record actually only had 1 level of subfolders!

    works great, thanks.
    LVL 12

    Expert Comment

    by:Habib Pourfard
    Try the following query:
    SET @ParentID = NULL
    ;WITH Tree
        SELECT ID, ParentID, FolderName, CAST(ID AS VARCHAR) AS Hierarchy, 0 AS Level
        FROM YourTable
        WHERE ISNULL(ParentID, -1) = ISNULL(@ParentID, -1)
        UNION ALL
        SELECT YourTable.ID, YourTable.ParentID, YourTable.FolderName, CAST(Tree.Hierarchy + '.' + CAST(YourTable.ID as VARCHAR) as VARCHAR), Level + 1
        FROM YourTable
        INNER JOIN Tree 
        ON YourTable.ParentID = Tree.ID
    SELECT Hierarchy, FolderName  FROM Tree ORDER BY Hierarchy

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now