Is it possible to flatten a parent-child hierarchy table to a flat/wide table using recursive CTE (Snowflake SQL)?

Question

I have a dumb/bad solution to my problem, but I have read the Snowflake docs on recursive CTEs (https://docs.snowflake.com/en/sql-reference/constructs/with and https://docs.snowflake.com/en/user-guide/queries-cte) and wanted to see if I could create a more readable/efficient solution using recursive CTEs. However, I am unable to get the result that I want. Have I misunderstood what is possible using recursive CTEs or is there a mistake in my code?

I have a Snowflake database and a table that looks like this:

parentmember_table

MemberId	ParentMemberId	MemberLevel
1	1	1
6	1	2
1007407	6	3
1010551	1007407	4

My goal is to "flatten" this table into the following table format:

flat_table

Level1	Leve2	Level3	Level4
1	6	1007407	1010551

(I have removed all the columns, from both tables, that I think are irrelevant to the question.)

Here is the dumb solution which gets me what I want (I can rewrite this using dbt/Jinja to avoid copy pasting every table):

use database MY_DATABASE;

create temporary table parentmember_table (memberid integer, parentmemberid integer, memberlevel integer);

INSERT INTO parentchild VALUES
  (1,       1,       1), -- root is where memberid = parentmemberid
  (6,       1,       2),
  (1007407, 6,       3),
  (1010551, 1007407, 4)
;


create or replace temporary table t0 as
select  parentmemberid as Level1
        , null as Level2
        , null as Level3
        , null as Level4
        , memberid
        , memberlevel
from    parentchild
where   parentmemberid = memberid
;


create or replace temporary table t1 as
select  a.Level1
        , case  when (b.memberlevel = 2) then b.memberid
                else coalesce(a.Level2, null)
                end as Level2
        
        , case  when (b.memberlevel = 3) then b.memberid
                else coalesce(a.Level3, null)
                end as Level3
        
        , case  when (b.memberlevel = 4) then b.memberid
                else coalesce(a.Level4, null)
                end as Level4
        
        , b.memberid as memberid
        , b.memberlevel as memberlevel
from    t0 as a
        , parentchild as b
where   b.parentmemberid = a.memberid
        and b.parentmemberid <> b.memberid
;


create or replace temporary table t2 as
select  a.Level1
        , case  when (b.memberlevel = 2) then b.memberid
                else coalesce(a.Level2, null)
                end as Level2
        
        , case  when (b.memberlevel = 3) then b.memberid
                else coalesce(a.Level3, null)
                end as Level3
        
        , case  when (b.memberlevel = 4) then b.memberid
                else coalesce(a.Level4, null)
                end as Level4
        
        , b.memberid as memberid
        , b.memberlevel as memberlevel
from    t1 as a
        , parentchild as b
where   b.parentmemberid = a.memberid
        and b.parentmemberid <> b.memberid
;

create or replace temporary table flat_table as
select  a.Level1
        , case  when (b.memberlevel = 2) then b.memberid
                else coalesce(a.Level2, null)
                end as Level2
        
        , case  when (b.memberlevel = 3) then b.memberid
                else coalesce(a.Level3, null)
                end as Level3
        
        , case  when (b.memberlevel = 4) then b.memberid
                else coalesce(a.Level4, null)
                end as Level4
        
        , b.memberid as memberid
        , b.memberlevel as memberlevel
from    t2 as a
        , parentchild as b
where   b.parentmemberid = a.memberid
        and b.parentmemberid <> b.memberid
;

Here is my attempt using recursive CTEs:

create or replace temporary table recursive_cte_table as (
WITH RECURSIVE t (
        Level1
        , Level2
        , Level3
        , Level4
        , MemberId
        , MemberLevel
        ) AS (

        --<anchor_clause>
        select  MemberId as Level1
                , null as Level2
                , null as Level3
                , null as Level4
                , MemberId
                , MemberLevel
        from    parentchild
        where   ParentMemberID = MemberID

        UNION ALL

        --<recursive_clause>
        select  a.Level1
                , case  when (b.MemberLevel = 2) then b.MemberId
                        end as Level2
                , case  when (b.MemberLevel = 3) then b.MemberId
                        end as Level3
                , case  when (b.MemberLevel = 4) then b.MemberId
                        end as Level4
                , b.MemberId as MemberId
                , b.MemberLevel as MemberLevel
        from    t as a
                , parentchild as b
        where   b.ParentMemberId = a.MemberId
                and b.ParentMemberId <> b.MemberId
)

SELECT * FROM t
);

This gives me 4 rows instead of 1 row, with NULL in most columns:

recursive_cte_table

Level1	Leve2	Level3	Level4	MemberId	MemberLevel
1	null	null	null	1	1
1	6	null	null	6	2
1	null	1007407	null	1007407	3
1	null	null	1010551	1010551	4

Is there a mistake in my code which can be fixed to make recursive_cte_table the same as flat_table? Or have I misunderstood what is possible using recursive CTEs?

EDIT: fixed table formatting.