I have the following table structure storing files and folder hierarchies.
FolderInfo table
Id (key)
Name
ParentFolderId (nullable)
and
File table
FileId(key)
FileName
FolderId
The FolderInfo table has a ParentFolderId which is a foreign key to its own id. The root folder has a null value in the ParentFolderId, all other records refer to their parent record through it.
I want to generate a result which contains the selected files and their complete path walking up the hierarchies.
Result
File
Location
I need to achieve this using some smart T-SQL. I would welcome if someone can show a common table expression based approach to recursively walk the hierarchy up.
For getting a single file's path you will use a recursive CTE of the form:
with folderPath as (
select Id, ParentFolderId, Name
from FolderInfo
where Id = @FileFolderId
union all select p.Id, p.ParentFolderId, p.Name
from folderPath p
join FolderInfo fi on fi.Id = p.ParentFolderId
)
select @FullFolderPath += '/' +Name from folderPath
where @FileFolderId is the Id value for the file you want the path for, and @FullFolderPath is the varchar(8000) variable to aggregate the path.
This will provide you with a good start, but it might need some tweaking.