Create A Database View In SQL Server That Will Contain The Path For A Given “Folder Id”

I have been trying to figure out how do determine the path to a given folder with a SQL query for a long time.  Finally I had a project where I really need this ability so I searched online for something similar. 

Eventually I found this:  Dump list of full paths of parent/child type records in SQL Server 2008 R2

Which I changed to look like this:

USE PWDemoMSSQL
GO
WITH folder_paths (o_projectno, folder_path)
AS
(
-- Anchor member definition
    SELECT f.o_projectno, cast('\'+f.o_projectname as varchar(max)) as folder_path
    FROM dbo.dms_proj AS f
    WHERE o_parentno=0
    UNION ALL
-- Recursive member definition
    SELECT f.o_projectno, cast(folder_path + '\' + f.o_projectname as varchar(max))
    FROM dbo.dms_proj AS f
    INNER JOIN folder_paths AS fp
        ON f.o_parentno = fp.o_projectno
)
-- Statement that executes the CTE
SELECT o_projectno, folder_path
FROM folder_paths
ORDER BY folder_path
GO

Here’s what it looks like along with the corresponding folder in ProjectWise Explorer:

To make this a bit more useful, I created a database view.  Here’s what I used:

USE PWDemoMSSQL
GO
CREATE VIEW vFolderPath AS
WITH folder_paths (o_projectno, folder_path)
AS
(
-- Anchor member definition
    SELECT f.o_projectno, cast('\'+f.o_projectname as varchar(max)) as folder_path
    FROM dbo.dms_proj AS f
    WHERE o_parentno=0
    UNION ALL
-- Recursive member definition
    SELECT f.o_projectno, cast(folder_path + '\' + f.o_projectname as varchar(max))
    FROM dbo.dms_proj AS f
    INNER JOIN folder_paths AS fp
        ON f.o_parentno = fp.o_projectno
)
SELECT o_projectno, folder_path
FROM folder_paths
GO

So now I can query for a specific folder like this:

SELECT folder_path FROM vFolderPath WHERE o_projectno=114;

For my purposes, I didn’t bother adding an index, but you might want to add one depending upon your needs. I suspect that something like this could be done with Oracle as well.  I’ll leave that for another day!