如何用单条T-SQL查询将SQL Server路径表转为HTML树形结构

现有SQL Server表，包含id和path两个字段，示例数据如下：

需要编写T-SQL脚本，生成由<ul>、<li>标签组成的HTML树形结构（以nvarchar类型输出），示例输出如下：

<ul><li>a<ul><li>b<ul><li>c<ul><li>d</li><li>e</li></ul></li></ul><ul></li><li>f<ul><li>g</li></ul></li></ul></li></ul><ul></li><li>h<ul><li>i</li><li>j</li></ul></li></ul></ul></li></ul>

该结构在HTML中呈现为标准树形层级效果，请问能否通过单条查询实现？

可以通过递归CTE（公用表表达式）结合字符串聚合实现单条查询生成目标HTML结构，以下是适配SQL Server 2017及以上版本的脚本（低版本可替换为FOR XML PATH实现聚合）：

WITH PathNodes AS (
    -- 基础节点：拆分根路径
    SELECT 
        id,
        path AS FullPath,
        CAST('<li>' + REPLACE(path, '/', '</li><ul><li>') + '</li>' AS NVARCHAR(MAX)) AS NodeHtml,
        LEN(path) - LEN(REPLACE(path, '/', '')) AS Depth,
        path AS ParentPath
    FROM YourTableName
    WHERE CHARINDEX('/', path) = 0 -- 根节点（无斜杠）
    UNION ALL
    -- 递归拆分子节点
    SELECT 
        c.id,
        c.path AS FullPath,
        CAST(p.NodeHtml + '<ul><li>' + REPLACE(RIGHT(c.path, LEN(c.path) - LEN(p.FullPath) - 1), '/', '</li><ul><li>') + '</li>' AS NVARCHAR(MAX)) AS NodeHtml,
        c.Depth,
        p.FullPath AS ParentPath
    FROM (
        SELECT 
            id,
            path,
            LEN(path) - LEN(REPLACE(path, '/', '')) AS Depth
        FROM YourTableName
        WHERE CHARINDEX('/', path) > 0 -- 非根节点
    ) c
    JOIN PathNodes p ON c.path LIKE p.FullPath + '/%' 
        AND c.Depth = p.Depth + 1
),
-- 筛选每个完整路径的最终节点（最深层级）
FinalNodes AS (
    SELECT FullPath, NodeHtml
    FROM PathNodes p
    WHERE NOT EXISTS (
        SELECT 1 FROM YourTableName 
        WHERE path LIKE p.FullPath + '/%'
    )
),
-- 聚合所有节点的HTML，并补充闭合的</ul>标签
TreeHtml AS (
    SELECT 
        CONCAT(
            '<ul>',
            STRING_AGG(NodeHtml, '') WITHIN GROUP (ORDER BY FullPath),
            REPLICATE('</ul>', (SELECT MAX(Depth) + 1 FROM PathNodes))
        ) AS HtmlTree
    FROM FinalNodes
)
SELECT HtmlTree FROM TreeHtml;

代码说明 #

1. 递归CTE PathNodes：拆分每个路径为层级化的节点，逐步拼接<li>和<ul>标签，同时记录每个节点的深度；
2. FinalNodes：筛选出每个分支的最末端节点，确保每个完整路径只保留最终拼接的HTML片段；
3. TreeHtml：用STRING_AGG聚合所有末端节点的HTML，再通过REPLICATE补充所有未闭合的</ul>标签，最终包裹最外层的<ul>。

低版本兼容（SQL Server 2016及以下） #

如果使用低于2017的版本，将TreeHtml部分替换为FOR XML PATH实现字符串聚合：

TreeHtml AS (
    SELECT 
        '<ul>' + 
        (SELECT NodeHtml FROM FinalNodes ORDER BY FullPath FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') +
        REPLICATE('</ul>', (SELECT MAX(Depth) + 1 FROM PathNodes)) AS HtmlTree
    FROM (SELECT 1) AS Dummy
)

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