Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1039
  • Last Modified:

SQL query to build folder tree

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.
  • 2
  • 2
1 Solution
";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
RustyZ32Author Commented:
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?
RustyZ32Author Commented:
Nevermind about folder levels comment, my test record actually only had 1 level of subfolders!

works great, thanks.
Habib PourfardSoftware DeveloperCommented:
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)
    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


Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now