如何将父子层级位置表转为各级名称分列展示？求JSON实现方案

适用环境 #

SQL Server 2016及更高版本（原生支持JSON函数）

实现逻辑 #

通过递归CTE遍历层级节点，每级节点将自身名称、编码、层级信息拼接为JSON数组传递到下一级，最终通过JSON解析函数直接提取各层级字段生成独立列。

完整代码示例 #

假设你的原表名为LocationHierarchy，包含字段Name（地址名称）、Code（地址唯一编码）、Parent（上级地址编码，顶级节点Parent值为NULL）：

WITH RecursiveLocation AS (
    -- 递归起点：顶级节点（国家层级）
    SELECT 
        Name,
        Code,
        Parent,
        1 AS LevelNum,
        -- 初始化JSON数组，存储当前节点信息
        CAST('[{"Name":"' + STRING_ESCAPE(Name, 'json') + '", "Code":"' + STRING_ESCAPE(Code, 'json') + '"}]' AS NVARCHAR(MAX)) AS FullPathJson
    FROM LocationHierarchy
    WHERE Parent IS NULL -- 顶级节点没有上级，按实际数据调整条件，比如Parent=0
    
    UNION ALL
    
    -- 递归遍历下级节点
    SELECT 
        l.Name,
        l.Code,
        l.Parent,
        r.LevelNum + 1 AS LevelNum,
        -- 把当前节点追加到JSON数组中
        CAST(LEFT(r.FullPathJson, LEN(r.FullPathJson)-1) + ',{"Name":"' + STRING_ESCAPE(l.Name, 'json') + '", "Code":"' + STRING_ESCAPE(l.Code, 'json') + '"}]' AS NVARCHAR(MAX))
    FROM LocationHierarchy l
    INNER JOIN RecursiveLocation r ON l.Parent = r.Code
)
-- 提取各层级字段，最多支持5级，可按实际层级数增减
SELECT
    JSON_VALUE(FullPathJson, '$[0].Name') AS 国家,
    JSON_VALUE(FullPathJson, '$[0].Code') AS 国家编码,
    JSON_VALUE(FullPathJson, '$[1].Name') AS 省,
    JSON_VALUE(FullPathJson, '$[1].Code') AS 省编码,
    JSON_VALUE(FullPathJson, '$[2].Name') AS 市/区,
    JSON_VALUE(FullPathJson, '$[2].Code') AS 市/区编码,
    JSON_VALUE(FullPathJson, '$[3].Name') AS 县/镇,
    JSON_VALUE(FullPathJson, '$[3].Code') AS 县/镇编码,
    JSON_VALUE(FullPathJson, '$[4].Name') AS 乡/街道,
    JSON_VALUE(FullPathJson, '$[4].Code') AS 乡/街道编码
FROM RecursiveLocation
-- 只输出最底层节点，不需要的话可以注释掉本行
WHERE LevelNum = (SELECT MAX(LevelNum) FROM RecursiveLocation)

说明 #

• 代码中使用STRING_ESCAPE函数转义JSON特殊字符，避免地址名称包含引号等特殊字符时JSON解析出错
• 如果你的层级数量不固定，可以动态拼接SELECT部分的提取逻辑，适配任意层级的需求
• 相比XML实现方案，JSON方法语法更简洁，可同时存储和提取Name、Code等多个字段，后续扩展字段不需要修改递归逻辑

内容的提问来源于stack exchange，提问作者question.it