TreeView from sql table of paths

So this is what I have came up with so far, however my parentID field is not populating correctly. Can anyone help?

ID      DirName                                  parentID         parentFolder
1      _catalogs                                        1                _catalogs
2      _catalogs/lt                                NULL        _catalogs
3      _catalogs/masterpage                  NULL        _catalogs
4      _catalogs/masterpage/Editing Menu      NULL        _catalogs/masterpage
5      _catalogs/masterpage/en-us            NULL        _catalogs/masterpage
6      _catalogs/masterpage/Forms            NULL        _catalogs/masterpage
7      _catalogs/users                                NULL        _catalogs
8      _catalogs/wp                                NULL        _catalogs
9      _catalogs/wt                                NULL        _catalogs
10      _cts                                             10                _cts

With x as (        
SELECT t1.DirName,ROW_NUMBER() 
        OVER (ORDER BY DirName) AS ID
FROM WSS_Content.dbo.AllDocs t1
LEFT OUTER JOIN WSS_Content.dbo.AllDocStreams t2 ON t1.ID = t2.ID
WHERE DirName <> '' AND  IsCurrentVersion=1 AND t1.SetupPath Is Null
GROUP BY DirName
), y as (
SELECT DISTINCT t1.DirName,SUBSTRING(t1.DirName, 0, CASE CHARINDEX('/', REVERSE(t1.DirName)) WHEN 0 THEN LEN(t1.DirName) + 1 ELSE LEN(t1.DirName) - CHARINDEX('/', REVERSE(t1.DirName)) +1 END) As ParentFolder
,ROW_NUMBER() 
        OVER (ORDER BY DirName) AS ID
FROM WSS_Content.dbo.AllDocs t1
LEFT OUTER JOIN WSS_Content.dbo.AllDocStreams t2 ON t1.ID = t2.ID
WHERE DirName <> '' AND  IsCurrentVersion=1 AND t1.SetupPath Is Null
GROUP BY DirName
),z as (
SELECT x.ID As parentID,x.ID
				 FROM x 
				 RIGHT OUTER JOIN y on y.ID = x.ID 
				 where y.ParentFolder =  x.DirName
)
SELECT x.ID,x.DirName,z.parentID,y.parentFolder
FROM x
LEFT OUTER JOIN y ON x.ID = y.ID
LEFT OUTER JOIN z ON x.ID = z.ParentID

Select allOpen in new window

The exact answer would require building your data model and trying your query on it, but for that you need to provide at least the schema of your two tables. It would be best if you posted the script with CREATE TABLE's and INSERT's, so we could easily have the your sample data, and then tried your query on it.

But generally speaking I would do this: I would first built your 3 tables x,y, and z as real tables, by running your 3 subqueries; then I'd review what's in these tables; if it's correct, then I'd move to the final query from these tables. This way, you isolate the problem onto pieces, so you'll more easily spot where it becomes wrong.

You do have a problem right here:

SELECT x.ID As parentID,x.ID
FROM x
RIGHT OUTER JOIN y
    on y.ID = x.ID
where y.ParentFolder =  x.DirName

If y.ID = x.ID, then they are the same folder.  Instead, try:

SELECT y.ID As parentID,x.ID
FROM x
LEFT OUTER JOIN y
    ON y.ParentFolder =  x.DirName


As an example, consider these two records in your sample data:
ID      DirName                  parentFolder
1      _catalogs                  _catalogs
2      _catalogs/lt               _catalogs

When Record ID 2 (_catalogs/lt) is the current record of x, it will join to record ID 1 through the parentfolder value matching the DirName value.  This seems to be what you are looking for.

This is what i get:

1      _catalogs                                        1        _catalogs
2      _catalogs/lt                                2       _catalogs
3      _catalogs/masterpage                  3      _catalogs
4      _catalogs/masterpage/Editing Menu      4      _catalogs/masterpage
5      _catalogs/masterpage/en-us            5      _catalogs/masterpage
6      _catalogs/masterpage/Forms            6      _catalogs/masterpage
7      _catalogs/users                                7       _catalogs
8      _catalogs/wp                                8       _catalogs
9      _catalogs/wt                                9       _catalogs
10      _cts                                              10        _cts

I guess I expected was:

1      _catalogs                                        1        _catalogs
2      _catalogs/lt                                1       _catalogs
3      _catalogs/masterpage                  1      _catalogs
4      _catalogs/masterpage/Editing Menu      3      _catalogs/masterpage
5      _catalogs/masterpage/en-us            3      _catalogs/masterpage
6      _catalogs/masterpage/Forms            3      _catalogs/masterpage
7      _catalogs/users                                1       _catalogs
8      _catalogs/wp                                1       _catalogs
9      _catalogs/wt                                1       _catalogs
10      _cts                                              10        _cts

I get an ambiguous error at Group By DirName under z and when I add the table it tells me i need to add the id field as well and when i add the id field i get:

ID      DirName                                 parentID      parentFolder
1      _catalogs                        1            _catalogs
2      _catalogs/lt                        NULL            _catalogs
3      _catalogs/masterpage                  NULL            _catalogs
4      _catalogs/masterpage/Editing Menu      NULL            _catalogs/masterpage
5      _catalogs/masterpage/en-us            NULL            _catalogs/masterpage
6      _catalogs/masterpage/Forms            NULL            _catalogs/masterpage
7      _catalogs/users                        NULL            _catalogs
8      _catalogs/wp                        NULL            _catalogs
9      _catalogs/wt                        NULL            _catalogs
10      _cts                              10            _cts

Can you do if isnull previous z.parentfolder?

You truly are a genius @bhess1