如何从SQL Server的多列层级扁平表中提取树形结构？

针对你的场景，我们可以用**递归CTE（Common Table Expression）**实现从扁平表到树形结构的转换，核心是先建立各层级的父子关联，再递归遍历生成树形结构。

假设你的表名为 FlatHierarchy，以下是完整解决方案： #

步骤1：用递归CTE构建父子层级关系

WITH HierarchyCTE AS (
    -- 锚点成员：提取所有唯一的一级节点（lvl1）
    SELECT 
        CAST(lvl1 AS VARCHAR(100)) AS NodeName,
        CAST(NULL AS VARCHAR(100)) AS ParentNode,
        1 AS Level
    FROM FlatHierarchy
    WHERE lvl1 IS NOT NULL
    GROUP BY lvl1

    UNION ALL

    -- 递归成员：依次关联二级到十级节点，建立父子关系
    SELECT
        CASE 
            WHEN Level = 1 THEN CAST(lvl2 AS VARCHAR(100))
            WHEN Level = 2 THEN CAST(lvl3 AS VARCHAR(100))
            WHEN Level = 3 THEN CAST(lvl4 AS VARCHAR(100))
            WHEN Level = 4 THEN CAST(lvl5 AS VARCHAR(100))
            WHEN Level = 5 THEN CAST(lvl6 AS VARCHAR(100))
            WHEN Level = 6 THEN CAST(lvl7 AS VARCHAR(100))
            WHEN Level = 7 THEN CAST(lvl8 AS VARCHAR(100))
            WHEN Level = 8 THEN CAST(lvl9 AS VARCHAR(100))
            WHEN Level = 9 THEN CAST(lvl10 AS VARCHAR(100))
        END AS NodeName,
        CASE 
            WHEN Level = 1 THEN CAST(lvl1 AS VARCHAR(100))
            WHEN Level = 2 THEN CAST(lvl2 AS VARCHAR(100))
            WHEN Level = 3 THEN CAST(lvl3 AS VARCHAR(100))
            WHEN Level = 4 THEN CAST(lvl4 AS VARCHAR(100))
            WHEN Level = 5 THEN CAST(lvl5 AS VARCHAR(100))
            WHEN Level = 6 THEN CAST(lvl6 AS VARCHAR(100))
            WHEN Level = 7 THEN CAST(lvl7 AS VARCHAR(100))
            WHEN Level = 8 THEN CAST(lvl8 AS VARCHAR(100))
            WHEN Level = 9 THEN CAST(lvl9 AS VARCHAR(100))
        END AS ParentNode,
        Level + 1 AS Level
    FROM FlatHierarchy
    JOIN HierarchyCTE h ON 
        (Level = 1 AND lvl2 IS NOT NULL AND h.NodeName = lvl1)
        OR (Level = 2 AND lvl3 IS NOT NULL AND h.NodeName = lvl2)
        OR (Level = 3 AND lvl4 IS NOT NULL AND h.NodeName = lvl3)
        OR (Level = 4 AND lvl5 IS NOT NULL AND h.NodeName = lvl4)
        OR (Level = 5 AND lvl6 IS NOT NULL AND h.NodeName = lvl5)
        OR (Level = 6 AND lvl7 IS NOT NULL AND h.NodeName = lvl6)
        OR (Level = 7 AND lvl8 IS NOT NULL AND h.NodeName = lvl7)
        OR (Level = 8 AND lvl9 IS NOT NULL AND h.NodeName = lvl8)
        OR (Level = 9 AND lvl10 IS NOT NULL AND h.NodeName = lvl9)
    GROUP BY 
        CASE WHEN Level =1 THEN lvl2 WHEN Level=2 THEN lvl3 WHEN Level=3 THEN lvl4 WHEN Level=4 THEN lvl5 WHEN Level=5 THEN lvl6 WHEN Level=6 THEN lvl7 WHEN Level=7 THEN lvl8 WHEN Level=8 THEN lvl9 WHEN Level=9 THEN lvl10 END,
        CASE WHEN Level =1 THEN lvl1 WHEN Level=2 THEN lvl2 WHEN Level=3 THEN lvl3 WHEN Level=4 THEN lvl4 WHEN Level=5 THEN lvl5 WHEN Level=6 THEN lvl6 WHEN Level=7 THEN lvl7 WHEN Level=8 THEN lvl8 WHEN Level=9 THEN lvl9 END,
        Level +1
)

步骤2：生成带缩进的树形输出

在上述CTE基础上，通过层级数控制缩进，生成目标树形格式：

SELECT 
    REPLICATE('  ', Level - 1) + '- ' + NodeName AS TreeOutput
FROM HierarchyCTE
ORDER BY 
    CASE Level 
        WHEN 1 THEN NodeName 
        WHEN 2 THEN ParentNode + NodeName 
        WHEN 3 THEN ParentNode + NodeName 
    END,
    Level, NodeName;

代码说明 #

1. 锚点成员：提取所有唯一的一级节点（lvl1）作为树形根节点，层级设为1。
2. 递归成员：从二级节点开始，将每一级的非null节点关联到上一级节点，建立父子关系，同时层级递增；用GROUP BY避免重复节点。
3. 树形输出：用REPLICATE(' ', Level -1)生成对应层级的缩进，拼接节点名称后得到带层级缩进的树形列表。

执行结果 #

针对你提供的示例数据，执行后会输出：

- Oracle
  - 营销单元
    - 互联网
    - 电视媒体
  - 法务单元
    - 知识产权部
    - 司法部门
  - 财务单元
  - IT单元
    - 数据库
    - ERP

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