hierarchy table to open all connections level into rows - snowflake

Question

I have an hierarchy table that includes connections between entities. for example:

entity_id	entity_type	resource_id	resource_type
1	X	2	Y
2	Y	3	Y
3	Y	4	Z

what i would like to get in the end:

entity_id	entity_type	resource_id	resource_type
1	X	2	Y
2	Y	3	Y
3	Y	4	Z
1	X	3	Y
1	Y	4	Z
2	Y	4	Z

I tried to join to itself with multi joins ( I know i have a max of 5 levels deep). but it only bring the final connections ( 1-4 for example) and not the between connections ( 2-4, 1-3)

Answer 1

You have to use recursive cte to get the desire result. find below the sql snippet

with cte as(
    select entity_id,entity_type,resource_id,resource_type from q1
    union all
    select cte.entity_id,cte.entity_type,q1.resource_id,q1.resource_type 
    from cte
    inner join q1 
    on cte.resource_id=q1.entity_id
)
select * from cte;