Keeping a constant identifying value at the beginning of a recursively unioning cte

Question

As part of a T-SQL project on a Microsoft SQL Server, I'm using a recursively unioning CTE to trace a series of child parent relationships through some hierarchical data to pull each individual element into a row that I can then pivot into a single row.

As an example, my recursively unioning CTE would turn the following rows from A (the source table) to B (the recursively unioned table) for four of the HCC values. C would be the desired outcome after the pivot.

A

MEMBER_NAME	PARENT_NAME
HCC_AS1	SEC_SMI
SEC_SMI	BRC_AND
BRC_AND	OBU_AND
HCC_AK4	BRC_BOZ
BRC_BOZ	OBU_LKY
HCC_BH1	OBU_BGH
HCC_Z5	SEC_LNG
SEC_LNG	BRC_LYN
BRC_LYN	OBU_LNM

B

MEMBER_NAME	PARENT_NAME	Hierarchy	Value
HCC_AS1	SEC_SMI	SEC	SMI
HCC_AS1	SEC_SMI	BRC	AND
HCC_AS1	SEC_SMI	OBU	AND
HCC_AK4	BRC_BOZ	BRC	BOZ
HCC_AK4	BRC_BOZ	OBU	LKY
HCC_BH1	OBU_BGH	OBU	BGH
HCC_Z5	SEC_LNG	SEC	LNG
HCC_Z5	SEC_LNG	BRC	LYN
HCC_Z5	SEC_LNG	OBU	LNM

C

MEMBER_NAME	PARENT_NAME	SEC	BRC	OBU
HCC_AS1	SEC_SMI	SMI	AND	AND
HCC_AK4	BRC_BOZ		BOZ	LKY
HCC_BH1	OBU_BGH			BGH
HCC_Z5	SEC_LNG	LNG	LYN	LNM

The only thing I'm having trouble with is preserving the original MEMBER_NAME and PARENT_NAME for the whole recursive CTE which is a requirement of the project. I wrapped it in a cursor and inserted into a temp table so I had something for them to look at but they want to change it to a view so I don't believe my current design will hold up. I'm not terribly proficient with recursive queries. Is there a solution that I'm missing that could be used to make this into a view?

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

I tried the cursor but that was when I thought this could just be a table that was built once a day. They want it to be a view though so I'd have to come up with a design that doesn't take a cursor.

Before I landed on a recursive CTE, I tried just doing a self joining table but the child parent relationship for the data is not consistent. Some HCC values don't have a SEC or BRC or both so just doing three self joins would sometimes land OBUs in the SEC or BRC columns.

Answer 1

Something like this perhaps:

WITH data AS (
SELECT  *
FROM    (
    VALUES  (N'HCC_AS1', N'SEC_SMI')
    ,   (N'SEC_SMI', N'BRC_AND')
    ,   (N'BRC_AND', N'OBU_AND')
    ,   (N'HCC_AK4', N'BRC_BOZ')
    ,   (N'BRC_BOZ', N'OBU_LKY')
    ,   (N'HCC_BH1', N'OBU_BGH')
    ,   (N'HCC_Z5', N'SEC_LNG')
    ,   (N'SEC_LNG', N'BRC_LYN')
    ,   (N'BRC_LYN', N'OBU_LNM')
) t (MEMBER_NAME,PARENT_NAME)
)
, cte AS (
    SELECT  MEMBER_NAME AS anchor, PARENT_NAME AS parent_anchor, PARENT_NAME AS parent, LEFT(PARENT_NAME, 3) AS hierarchy, RIGHT(PARENT_NAME, 3) AS Value
    FROM    data d
    WHERE   NOT EXISTS(
            SELECT  1
            FROM    data d2
            WHERE   d2.parent_name = d.member_name
        )
    UNION ALL
    SELECT  anchor, parent_anchor, d.parent_name, LEFT(PARENT_NAME, 3) AS hierarchy, RIGHT(PARENT_NAME, 3) AS Value
    FROM    cte c
    INNER JOIN data d
        ON  d.MEMBER_NAME = c.parent
)
SELECT  *
FROM    (
    SELECT  anchor, parent_anchor, hierarchy, value
    FROM    cte
    ) x
PIVOT (max(value) FOR hierarchy IN (SEC, BRC,OBU)) y

1. I create two columns which are kept as anchors and don't change from the top
2. Then one can do the standard recursive CTE thing for the rest of the columns.
3. Finally, for the pivot, i remove the unnecessary parent column and create the pivot from the rest of the fields.

Output:

anchor	parent_anchor	SEC	BRC	OBU
HCC_AK4	BRC_BOZ	NULL	BOZ	LKY
HCC_BH1	OBU_BGH	NULL	NULL	BGH
HCC_Z5	SEC_LNG	LNG	LYN	LNM
HCC_AS1	SEC_SMI	SMI	AND	AND