SQL Server 2008 CTE 递归

我正在尝试使用 SQL Server 2008 的 CTE 来执行我认为是困难的递归。我似乎无法理解这个问题。

在下面的示例中，您可以假设固定深度为 3...不会低于该深度。在现实生活中，深度“更深”但仍然是固定的。在这个例子中我试图简化它一些。

我的输入数据如下所示。

ID     PARENT_ID       NAME          DEPTH
------------------------------------------
1      NULL            A             1
2      1               B             2
3      2               C             3
4      1               D             2

我的 CTE 的输出应该如下表。

LEVEL1_ID    LEVEL2_ID    LEVEL3_ID    LEVEL1_NAME    LEVEL2_NAME    LEVEL3_NAME
--------------------------------------------------------------------------------
1            NULL         NULL         A              NULL           NULL
1            2            NULL         A              B              NULL
1            2            3            A              B              C
1            4            NULL         A              D              NULL

如果我可以在输出中获取 ID 列，我当然可以映射到查找表中的名称。

我也愿意接受其他实现此目的的方法，包括使用 SSIS。

其实并不是那么难做到：

;WITH cte AS
(
    SELECT CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID
    FROM dbo.YourTable
    WHERE parent_id IS NULL

    UNION ALL

    SELECT CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID
    FROM dbo.YourTable t
    INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY ID

给我一个输出：

/A
/A/B
/A/B/C
/A/D

旁注：“深度”可以通过 CTE 轻松计算，您不一定需要将其存储在表中（请参阅Level我添加的专栏）：

;WITH cte AS
(
    SELECT 
       CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID, 
       1 AS 'Level'
    FROM dbo.YourTable
    WHERE parent_id IS NULL

    UNION ALL

    SELECT 
       CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID,
       cte.Level + 1 AS 'Level'
    FROM dbo.YourTable t
    INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY Level, ID