Add folder name to filename with SQL Server

Question

I'm currently using this to get the directory / filenames, but need to be able to add the overall folder name onto the filename.

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
   DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree
(
     id INT IDENTITY(1 ,1)
    ,subdirectory NVARCHAR(512)
    ,depth INT
    ,isfile BIT
);

INSERT #DirectoryTree (subdirectory, depth, isfile)
    EXEC master.sys.xp_dirtree 'X:\KO Contracts\Contracts', 3, 1;

Running:

SELECT *
FROM #DirectoryTree AS dt

Returns:

id  subdirectory           depth  isfile
1   7761601                  1      0
2   Documents                2      0
3   12 Month Program.pdf     3      1
4   7764478                  1      0
5   Documents                2      0
6   12 Month Program.pdf     3      1
7   7773224                  1      0
8   Documents                2      0
9   12 Month Program.pdf     3      1
10  12Month PT.pdf           3      1
11  6 Month Program.pdf      3      1

What I need is to rename the file (isfile = 1) with the directory it is in (depth = 1).

Examples:

7761601_12 Month Program.pdf
7764478_12 Month Program.pdf
7773224_12 Month Program.pdf
7773224_12Month PT.pdf
7773224_6 Month Program.pdf

Answer 1

;WITH v AS (
    SELECT
        *
    FROM
        (VALUES
        (1,'7761601',1,0),
        (2,'Documents',2,0),
        (3,'12 Month Program.pdf',3,1),
        (4,'7764478',1,0),
        (5,'Documents',2,0),
        (6,'12 Month Program.pdf',3,1),
        (7,'7773224',1,0),
        (8,'Documents',2,0),
        (9,'12 Month Program.pdf',3,1),
        (10,'12Month PT.pdf',3,1),
        (11,'6 Month Program.pdf',3,1)) AS i(id,subdirectory,depth,isfile)
),
folder_root AS (
    SELECT
        o.id,
        folder_root_id=MAX(i.id)
    FROM
        v AS o
        INNER JOIN v AS i ON
            i.isfile=0 AND
            i.depth=1 AND
            i.id<o.id
    WHERE
        o.isfile=1
    GROUP BY
        o.id
)
SELECT
    file_name=folder_name.subdirectory+'_'+v.subdirectory
FROM
    v
    INNER JOIN folder_root AS fr ON
        fr.id=v.id
    INNER JOIN v AS folder_name ON
        folder_name.id=fr.folder_root_id;

Result:

+------------------------------+
|          file_name           |
+------------------------------+
| 7761601_12 Month Program.pdf |
| 7764478_12 Month Program.pdf |
| 7773224_12 Month Program.pdf |
| 7773224_12Month PT.pdf       |
| 7773224_6 Month Program.pdf  |
+------------------------------+