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?