As part of a TSQL 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 to B for one of the HCC values. C would be the desired outcome after the pivot.

A:

B:
| MEMBER_NAME | PARENT_NAME | Hierarchy | Value |
| ———– | ———– | ——– | ——– |
|HCC_AS1 | SEC_SMI | SEC | SMI |
|SEC_SMI | SEC_SMI | BRC | AND |
|BRC_AND | SEC_SMI | OBU | AND |

C:
| MEMBER_NAME | PARENT_NAME | SEC | BRC | OBU |
| ———– | ———– | ——– | ——– | ———- |
|HCC_AS1 | SEC_SMI | SMI | AND | AND |

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.