SQL Server递归Union CTE保留初始标识值，转游标实现为视图

场景说明 #

在Microsoft SQL Server的T-SQL项目中，需通过递归Union CTE追踪层级数据的父子关系，将每个层级元素提取为单独行后转置。以下是数据流转示例：

源表A（原始层级数据） #

递归联合后的表B（层级拆分结果） #

转置后的目标表C（最终需求格式） #

核心问题 #

必须在整个递归过程中保留原始的MEMBER_NAME和PARENT_NAME，且需将原游标实现改为视图（游标无法适配视图需求）。此前尝试自连接表，但因部分HCC缺少SEC/BRC层级，导致列错位。

原游标实现代码 #

DECLARE @member varchar(80)
DECLARE @parent varchar(80)

CREATE TABLE #Temp
(
    MEMBER_NAME varchar(80),
    PARENT_NAME varchar(80),
    Hierarchy varchar(80),
    [Value] varchar(80)
);
 
DECLARE member_cursor CURSOR FOR 
    SELECT
        [MEMBER_NAME], [PARENT_NAME]
    FROM 
        [CACHED_OUTLINE_MEMBERS]
    WHERE 
        DIMENSION_NAME = 'Delivery_Center'
        AND LEFT(MEMBER_NAME, 3) = 'HCC'
        AND LEFT(PARENT_NAME, 3) IN ('SEC', 'OBU', 'BRC')
 
OPEN member_cursor
 
FETCH NEXT FROM member_cursor INTO @member, @parent
 
WHILE @@FETCH_STATUS = 0
BEGIN
    WITH Parent AS
    (
        SELECT
            LEFT(PARENT_NAME, CHARINDEX('_', Parent_name) -1) AS Hierarchy,
            RIGHT(PARENT_NAME, LEN(parent_name) - CHARINDEX('_', Parent_name)) AS [Value]
        FROM 
            [CACHED_OUTLINE_MEMBERS]
        WHERE
            MEMBER_NAME = @member
 
        UNION ALL
 
        SELECT
            LEFT(C.PARENT_NAME, CHARINDEX('_', C.Parent_name) -1) AS Hierarchy, 
            RIGHT(C.PARENT_NAME, LEN(C.parent_name) - CHARINDEX('_', C.Parent_name)) AS [Value]
        FROM 
            [CACHED_OUTLINE_MEMBERS] C
        INNER JOIN 
            Parent ON C.member_name = parent.parent_name
        WHERE
            LEFT(C.PARENT_NAME, CHARINDEX('_', C.Parent_name) -1) IN ('SEC', 'OBU', 'BRC')
    )
    INSERT INTO #Temp
        SELECT 
            @member AS MEMBER_NAME, @parent AS PARENT_NAME, Hierarchy, [Value]  
        FROM
            Parent
 
    FETCH NEXT FROM member_cursor INTO @member, @parent 
END
 
CLOSE member_cursor
DEALLOCATE member_cursor
 
SELECT * FROM #Temp

解决方案：无游标视图实现 #

完整视图代码 #

CREATE VIEW vw_DeliveryCenter_Hierarchy
AS
WITH HierarchyCTE AS (
    -- 锚点成员：选取所有HCC节点，记录原始成员和父节点
    SELECT 
        cm.MEMBER_NAME AS root_member,
        cm.PARENT_NAME AS root_parent,
        LEFT(cm.PARENT_NAME, CHARINDEX('_', cm.PARENT_NAME) - 1) AS Hierarchy,
        RIGHT(cm.PARENT_NAME, LEN(cm.PARENT_NAME) - CHARINDEX('_', cm.PARENT_NAME)) AS [Value],
        cm.PARENT_NAME AS current_parent
    FROM [CACHED_OUTLINE_MEMBERS] cm
    WHERE 
        cm.DIMENSION_NAME = 'Delivery_Center'
        AND LEFT(cm.MEMBER_NAME, 3) = 'HCC'
        AND LEFT(cm.PARENT_NAME, 3) IN ('SEC', 'OBU', 'BRC')

    UNION ALL

    -- 递归成员：向上遍历父节点的层级，保留原始HCC节点信息
    SELECT 
        h.root_member,
        h.root_parent,
        LEFT(cm.PARENT_NAME, CHARINDEX('_', cm.PARENT_NAME) - 1) AS Hierarchy,
        RIGHT(cm.PARENT_NAME, LEN(cm.PARENT_NAME) - CHARINDEX('_', cm.PARENT_NAME)) AS [Value],
        cm.PARENT_NAME AS current_parent
    FROM [CACHED_OUTLINE_MEMBERS] cm
    INNER JOIN HierarchyCTE h ON cm.MEMBER_NAME = h.current_parent
    WHERE LEFT(cm.PARENT_NAME, 3) IN ('SEC', 'OBU', 'BRC')
)
-- 转置层级数据为目标格式
SELECT 
    root_member AS MEMBER_NAME,
    root_parent AS PARENT_NAME,
    ISNULL(SEC, '') AS SEC,
    ISNULL(BRC, '') AS BRC,
    ISNULL(OBU, '') AS OBU
FROM HierarchyCTE
PIVOT (
    MAX([Value])
    FOR Hierarchy IN ([SEC], [BRC], [OBU])
) AS PivotTable
GO

方案说明 #

1. 递归CTE逻辑：锚点成员直接选取所有HCC节点，同时将其原始MEMBER_NAME和PARENT_NAME标记为root_member和root_parent，递归过程中始终携带这两个字段，确保所有层级记录都关联到原始HCC节点。
2. 转置处理：使用PIVOT按层级类型转置，通过MAX([Value])聚合（每个层级对应唯一值），并用ISNULL将缺失层级的空值转为空字符串，避免列错位问题。
3. 视图适配：整个逻辑封装为视图，无需临时表或游标，完全符合视图的无状态要求。

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