What's a proper way to "unroll" a self-referencing table with a variable depth and concatenate the levels with an associated value from another table?
table 1: FILENAME
filename folder_id size
-----------------------------
toplevel1.txt 1 1
onedeep1.txt 2 2
twodeep.txt 3 4
toplevel2.txt 1 4
onedeep2.txt 4 3
table 2: FOLDERS
folder_id folder_name parent_folder_id
----------------------------------------
0 ROOT NULL
1 temp 0
2 subdir1 1
3 subsubdir 2
4 subdir2 1
Desired output:
path size
----------------------------------------
temp\toplevel1.txt 1
temp\subdir1\onedeep1.txt 2
temp\subdir1\subsubdir\twodeep.txt 4
temp\toplevel2.txt 4
temp\subdir2\onedeep2.txt 3
Have tried (and failed) using while loops, ctes, and cursors. Seems relatively straight forward, but I can't get a handle on the variable depth of the paths…
Best Answer
Use a recursive CTE to expand the folder names to full paths:
For your example, the CTE will produce this output:
As you can probably guess now, you can just join the
Filename
table to the CTE and use itsfolder_path
column to get thepath
column of your output:This is the output:
A live demo of this solution is available at Rextester.