如何将Exact软件的glclassifications层级表扁平化为含8级子节点的视图？

我明白你的需求：要把glclassifications这个层级表转成扁平化的视图，包含从父节点到8个子节点的所有ID、编码和描述字段，而且不能用临时表，还要保证哪怕实际层级不够8级，对应的字段也得存在。

你写的递归CTE已经能遍历所有节点了，但缺少对每一层级字段的追踪。接下来我们把它扩展一下，让递归过程中记录每一级的信息，最后映射成你需要的字段名：

完整的视图创建脚本 #

-- 可选：物理删除原表中重复的ID记录（执行一次即可）
WITH CTE_Deduplicate AS (
 SELECT 
     ID,
     ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0)) RN
 FROM [dbo].[glclassifications]
)
DELETE FROM CTE_Deduplicate WHERE RN > 1;

-- 创建扁平化视图
CREATE VIEW [dbo].[vw_glclassifications_flattened]
AS
WITH cte_deduplicated AS (
    -- 逻辑去重（如果已经物理删除重复，可简化为直接查询原表）
    SELECT 
        ID, 
        Code, 
        Description, 
        Parent
    FROM (
        SELECT 
            ID, 
            Code, 
            Description, 
            Parent,
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0)) AS rn
        FROM [dbo].[glclassifications]
    ) t
    WHERE rn = 1
),
cte_recursive AS (
    -- 初始化根节点（Parent为空或长度为0），所有子节点字段先设为NULL
    SELECT
        ID AS L1_ID,
        Code AS L1_Code,
        Description AS L1_Desc,
        CAST(NULL AS NVARCHAR(500)) AS L2_ID,
        CAST(NULL AS NVARCHAR(500)) AS L2_Code,
        CAST(NULL AS NVARCHAR(500)) AS L2_Desc,
        CAST(NULL AS NVARCHAR(500)) AS L3_ID,
        CAST(NULL AS NVARCHAR(500)) AS L3_Code,
        CAST(NULL AS NVARCHAR(500)) AS L3_Desc,
        CAST(NULL AS NVARCHAR(500)) AS L4_ID,
        CAST(NULL AS NVARCHAR(500)) AS L4_Code,
        CAST(NULL AS NVARCHAR(500)) AS L4_Desc,
        CAST(NULL AS NVARCHAR(500)) AS L5_ID,
        CAST(NULL AS NVARCHAR(500)) AS L5_Code,
        CAST(NULL AS NVARCHAR(500)) AS L5_Desc,
        CAST(NULL AS NVARCHAR(500)) AS L6_ID,
        CAST(NULL AS NVARCHAR(500)) AS L6_Code,
        CAST(NULL AS NVARCHAR(500)) AS L6_Desc,
        CAST(NULL AS NVARCHAR(500)) AS L7_ID,
        CAST(NULL AS NVARCHAR(500)) AS L7_Code,
        CAST(NULL AS NVARCHAR(500)) AS L7_Desc,
        CAST(NULL AS NVARCHAR(500)) AS L8_ID,
        CAST(NULL AS NVARCHAR(500)) AS L8_Code,
        CAST(NULL AS NVARCHAR(500)) AS L8_Desc,
        CAST(NULL AS NVARCHAR(500)) AS L9_ID,
        CAST(NULL AS NVARCHAR(500)) AS L9_Code,
        CAST(NULL AS NVARCHAR(500)) AS L9_Desc,
        ID AS Current_ID,
        1 AS Depth
    FROM cte_deduplicated
    WHERE Parent IS NULL OR LEN(Parent) = 0

    UNION ALL

    -- 递归遍历子节点，逐层填充对应层级的字段
    SELECT
        r.L1_ID,
        r.L1_Code,
        r.L1_Desc,
        CASE WHEN r.Depth = 1 THEN c.ID ELSE r.L2_ID END,
        CASE WHEN r.Depth = 1 THEN c.Code ELSE r.L2_Code END,
        CASE WHEN r.Depth = 1 THEN c.Description ELSE r.L2_Desc END,
        CASE WHEN r.Depth = 2 THEN c.ID ELSE r.L3_ID END,
        CASE WHEN r.Depth = 2 THEN c.Code ELSE r.L3_Code END,
        CASE WHEN r.Depth = 2 THEN c.Description ELSE r.L3_Desc END,
        CASE WHEN r.Depth = 3 THEN c.ID ELSE r.L4_ID END,
        CASE WHEN r.Depth = 3 THEN c.Code ELSE r.L4_Code END,
        CASE WHEN r.Depth = 3 THEN c.Description ELSE r.L4_Desc END,
        CASE WHEN r.Depth = 4 THEN c.ID ELSE r.L5_ID END,
        CASE WHEN r.Depth = 4 THEN c.Code ELSE r.L5_Code END,
        CASE WHEN r.Depth = 4 THEN c.Description ELSE r.L5_Desc END,
        CASE WHEN r.Depth = 5 THEN c.ID ELSE r.L6_ID END,
        CASE WHEN r.Depth = 5 THEN c.Code ELSE r.L6_Code END,
        CASE WHEN r.Depth = 5 THEN c.Description ELSE r.L6_Desc END,
        CASE WHEN r.Depth = 6 THEN c.ID ELSE r.L7_ID END,
        CASE WHEN r.Depth = 6 THEN c.Code ELSE r.L7_Code END,
        CASE WHEN r.Depth = 6 THEN c.Description ELSE r.L7_Desc END,
        CASE WHEN r.Depth = 7 THEN c.ID ELSE r.L8_ID END,
        CASE WHEN r.Depth = 7 THEN c.Code ELSE r.L8_Code END,
        CASE WHEN r.Depth = 7 THEN c.Description ELSE r.L8_Desc END,
        CASE WHEN r.Depth = 8 THEN c.ID ELSE r.L9_ID END,
        CASE WHEN r.Depth = 8 THEN c.Code ELSE r.L9_Code END,
        CASE WHEN r.Depth = 8 THEN c.Description ELSE r.L9_Desc END,
        c.ID AS Current_ID,
        r.Depth + 1 AS Depth
    FROM cte_recursive r
    INNER JOIN cte_deduplicated c ON r.Current_ID = c.Parent
    WHERE r.Depth < 9 -- 限制最多递归到第9级，对应8个子节点
)
-- 将层级字段重命名为你需要的Parent/Child格式
SELECT
    L1_ID AS Parent_ID,
    L1_Code AS Parent_Code,
    L1_Desc AS Parent_Description,
    L2_ID AS Child1_ID,
    L2_Code AS Child1_Code,
    L2_Desc AS Child1_Description,
    L3_ID AS Child2_ID,
    L3_Code AS Child2_Code,
    L3_Desc AS Child2_Description,
    L4_ID AS Child3_ID,
    L4_Code AS Child3_Code,
    L4_Desc AS Child3_Description,
    L5_ID AS Child4_ID,
    L5_Code AS Child4_Code,
    L5_Desc AS Child4_Description,
    L6_ID AS Child5_ID,
    L6_Code AS Child5_Code,
    L6_Desc AS Child5_Description,
    L7_ID AS Child6_ID,
    L7_Code AS Child6_Code,
    L7_Desc AS Child6_Description,
    L8_ID AS Child7_ID,
    L8_Code AS Child7_Code,
    L8_Desc AS Child7_Description,
    L9_ID AS Child8_ID,
    L9_Code AS Child8_Code,
    L9_Desc AS Child8_Description
FROM cte_recursive
-- 可选：如果只需要叶子节点（无下级节点），添加以下条件
-- WHERE NOT EXISTS (SELECT 1 FROM cte_deduplicated c WHERE c.Parent = Current_ID)
GO

这个方案的优势 #

1. 无临时表：完全用CTE和视图实现，符合你的设计原则，避免了临时表带来的维护问题
2. 自动处理重复：既支持物理删除原表重复数据，也保留了逻辑去重的逻辑，确保数据唯一性
3. 字段齐全：不管实际层级有多少，都会生成从Parent到Child8的所有字段，不足的层级用NULL填充
4. 实时更新：视图是基于原表的实时查询，原表数据变化后视图会自动同步，不需要手动刷新
5. 可扩展性：如果以后需要增加层级，只需要修改递归深度和对应的字段即可

注意事项 #

• 如果你已经执行了物理删除重复数据的操作，可以把cte_deduplicated简化成直接查询原表，去掉去重逻辑
• 递归深度限制为9级，对应8个子节点，刚好满足你的需求
• 如果需要包含所有节点（包括中间层级的节点），就去掉最后的WHERE NOT EXISTS条件；如果只需要叶子节点，就加上它

内容的提问来源于stack exchange，提问作者Henrov