使用SQL Server将文件夹名称添加到filename

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).

我需要的是重命名文件(isfile = 1)与它所在的目录(深度= 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

2 个解决方案

#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  |
+------------------------------+

#2

You can do this using a recursive CTE, like this:

您可以使用递归CTE执行此操作,如下所示:

;WITH CTE_Rollup AS
(
    SELECT
        id,
        id AS base_file_id,
        CAST(subdirectory AS NVARCHAR(MAX)) AS full_path,
        subdirectory AS new_filename,
        depth
    FROM
        #DirectoryTree
    WHERE
        isfile = 1
    UNION ALL
    SELECT
        DT.id,
        R.base_file_id,
        CAST(DT.subdirectory + '\' + R.full_path AS NVARCHAR(MAX)),
        CAST(CASE WHEN R.full_path = R.new_filename THEN DT.subdirectory + '_' + R.new_filename ELSE R.new_filename END AS NVARCHAR(512)) AS new_filename,
        DT.depth
    FROM
        CTE_Rollup R
    INNER JOIN #DirectoryTree DT ON DT.id = R.id - 1 AND DT.depth = R.depth - 1
)
SELECT
    base_file_id,
    full_path,
    new_filename
FROM CTE_Rollup R
WHERE
    R.depth = 1

The new_filename works by making sure that we only change it if we're exactly one level up from the file (and so the new_filename and full_path are still equal at that point.)

new_filename的工作方式是确保我们只改变它,如果我们只是从文件向上一级(因此new_filename和full_path在该点仍然相等。)

I've also included the full_path as that might be useful in a rename operation.

我还包括了full_path,因为它可能在重命名操作中很有用。

Two very important things to consider though...

要考虑的两个非常重要的事情......

1. xp_dirtree is neither documented nor supported, so it's not a good idea to use it in production code.

   xp_dirtree既未记录也未受支持,因此在生产代码中使用它并不是一个好主意。

2. This seems like an operation that is much better suited to a front end process rather than within the database.

   这看起来像是一个更适合前端进程而不是数据库的操作。