如何用SQL递归CTE+XML拼接层级库存位置为单字符串

我来帮你搞定这个层级字符串拼接的需求！你已经用游标实现了功能，现在想用递归CTE结合STUFF和XML来优化，这个思路非常高效，下面是具体的实现方案，完全适配你的场景：

核心思路 #

我们按照Department → Rack → Shelf → Rows的层级从下往上聚合：

1. 先把每个Shelf对应的所有Rows合并成逗号分隔的字符串
2. 再把同一个Rack下的所有Shelf描述合并
3. 接着把同一个Department下的所有Rack描述合并
4. 最后把所有Department的结果拼接成单行标签文本

完整SQL代码 #

DECLARE @partnumber varchar(50) = '1BODY000997';

WITH HierarchyCTE AS (
    -- 基础层：处理每个Shelf对应的Rows拼接
    SELECT 
        Department_Name,
        Rack_Name,
        Shelf_Name,
        -- 生成Shelf级的描述文本
        ShelfString = CONCAT('Shelf:', Shelf_Name, ' (Row ', 
            STUFF((
                SELECT DISTINCT ',' + Row_Name 
                FROM Part_Locations pl2 
                WHERE pl2.Part_Number = pl1.Part_Number
                  AND pl2.Department_Name = pl1.Department_Name
                  AND pl2.Rack_Name = pl1.Rack_Name
                  AND pl2.Shelf_Name = pl1.Shelf_Name
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''),
            ')')
    FROM Part_Locations pl1
    WHERE pl1.Part_Number = @partnumber
    GROUP BY Department_Name, Rack_Name, Shelf_Name

    UNION ALL

    -- 递归层1：合并同一Rack下的所有Shelf描述
    SELECT 
        Department_Name,
        Rack_Name,
        NULL AS Shelf_Name, -- 标记当前为Rack层级
        RackString = CONCAT('Rack:', Rack_Name, ' - ', 
            STUFF((
                SELECT ' ' + ShelfString 
                FROM HierarchyCTE h2 
                WHERE h2.Department_Name = h1.Department_Name
                  AND h2.Rack_Name = h1.Rack_Name
                  AND h2.Shelf_Name IS NOT NULL
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''))
    FROM HierarchyCTE h1
    WHERE h1.Shelf_Name IS NOT NULL
    GROUP BY Department_Name, Rack_Name

    UNION ALL

    -- 递归层2：合并同一Department下的所有Rack描述
    SELECT 
        Department_Name,
        NULL AS Rack_Name, -- 标记当前为Department层级
        NULL AS Shelf_Name,
        DeptString = CONCAT(Department_Name, ' - ', 
            STUFF((
                SELECT ' ' + RackString 
                FROM HierarchyCTE h2 
                WHERE h2.Department_Name = h1.Department_Name
                  AND h2.Rack_Name IS NOT NULL
                  AND h2.Shelf_Name IS NULL
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''))
    FROM HierarchyCTE h1
    WHERE h1.Rack_Name IS NOT NULL
      AND h1.Shelf_Name IS NULL
    GROUP BY Department_Name
)
-- 最终合并所有Department的结果为单行字符串
SELECT STUFF((
    SELECT ' ' + DeptString 
    FROM HierarchyCTE 
    WHERE Rack_Name IS NULL 
      AND Shelf_Name IS NULL
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') AS FullLocationString;

代码解释 #

• 基础层：从你的Part_Locations视图中分组获取每个Department-Rack-Shelf组合，用STUFF+XML将当前Shelf下的Rows合并成逗号分隔的字符串，生成类似Shelf:A (Row 1,2,3...)的文本。
• 第一层递归：基于基础层结果，按Department-Rack分组，把同一个Rack下的所有Shelf描述拼接起来，生成类似Rack:IA-10 - Shelf:A (...) Shelf:B (...)的文本。
• 第二层递归：基于Rack级结果，按Department分组，把同一个Department下的所有Rack描述拼接起来，生成类似Assembly - Rack:IA-10 - ...的文本。
• 最终查询：把所有Department级的描述合并成单行，就是你需要的标签展示文本。

注意事项 #

• 使用TYPE.value('.', 'varchar(max)')可以避免特殊字符（如&、<、>）被XML转义，保证文本的准确性。
• 递归中的NULL标记（Shelf_Name/Rack_Name为NULL）用于区分不同层级的聚合结果，防止递归循环。
• 如果存在多个Department，这段代码会自动将所有Department的描述拼接在一起，完全适配你的多部门场景。

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