如何将SQL Server递归BOM查询结果转换为标准层级格式？

我正在使用SQL Server 15版本的递归查询从数据库中提取物料清单（Bill of Materials，BOM）。PST表包含过多列无法逐一列举，本次查询仅使用关键列：[PST_ParentItemID]为父项，[PST_CompItemID]为子项。

现有递归查询语句 #

WITH BillOfMaterialsCTE AS (
SELECT PST_ParentItemID AS Parent, PST_CompItemID AS Child, 0 AS 
level
FROM dbo.PST WHERE PST_ParentItemID = '601-2120-002'

UNION ALL

SELECT b.PST_ParentItemID, b.PST_CompItemID, cte.level + 1
FROM dbo.PST b
INNER JOIN BillOfMaterialsCTE cte ON b.PST_ParentItemID = cte.Child
)
SELECT row_number() OVER (ORDER BY level, Parent) id, Parent, 
Child, level
FROM BillOfMaterialsCTE
ORDER BY level, Parent

当前查询结果 #

期望输出格式 #

请问应如何修改现有查询语句以实现该需求？

解决方案 #

要实现目标格式，需要调整递归CTE的逻辑：先包含根节点，再在递归过程中跟踪每个节点的父项标识，最后通过映射关联父子ID。修改后的SQL如下：

WITH BOMHierarchy AS (
    -- 初始化根节点
    SELECT 
        '601-2120-002' AS Item,
        NULL AS ParentItem,
        0 AS level
    UNION ALL
    -- 递归获取所有子节点，记录父项的Item值
    SELECT 
        b.PST_CompItemID AS Item,
        b.PST_ParentItemID AS ParentItem,
        bh.level + 1 AS level
    FROM dbo.PST b
    INNER JOIN BOMHierarchy bh ON b.PST_ParentItemID = bh.Item
),
-- 为每个节点生成唯一ID，建立Item与ID的映射
BOMWithIDs AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY level, Item) AS id,
        Item,
        ParentItem
    FROM BOMHierarchy
)
-- 关联父项ID，生成最终格式
SELECT 
    current.id,
    current.Item,
    parent.id AS [Parent ID]
FROM BOMWithIDs current
LEFT JOIN BOMWithIDs parent ON current.ParentItem = parent.Item
ORDER BY current.id;

逻辑说明 #

1. 根节点初始化：在CTE初始部分直接加入根物料601-2120-002，其父项设为NULL，层级为0。
2. 递归遍历子节点：从PST表中递归查询所有子项，同时记录每个子项对应的父项Item值。
3. 生成节点ID：通过ROW_NUMBER()按层级和Item排序，为每个节点生成连续唯一的ID。
4. 关联父ID：通过自连接将每个节点的ParentItem映射到对应的父节点ID，得到标准层级格式。

内容的提问来源于stack exchange，提问作者Sudenga-Steve