Combine parent id and all child ids in an array in a single row using recursive cte

Question

I have a table pr with two columns:

stg source id, parent id

125, 124
126, 125
127, 125
128, 127 

what I want a final result as:

parent id, stg_source_ids
124, [125, 126, 127, 128] 

as my final row in another view.

I am trying to solve this using recursive cte

WITH RECURSIVE ParentChildHierarchy AS (
    SELECT pr.parent_id, pr.stg_source_id, 
    ARRAY_CONSTRUCT(pr.stg_source_id) AS stg_source_ids
    FROM pr
    WHERE NOT EXISTS (SELECT 1 FROM pr as pr2 WHERE 
    pr2.stg_source_id = pr.parent_id)

UNION ALL

SELECT pr.parent_id, pr.stg_source_id, 
array_append(p.stg_source_ids, pr.stg_source_id)
FROM pr JOIN ParentChildHierarchy p 
ON pr.parent_id = p.stg_source_id

)

this produce results such as:

124 -- [125]
125 -- [125, 126]
125 -- [125, 127]
127 -- [127, 128]

Answer 1

You can try listagg function in Snowflake,

WITH CTE as (
SELECT pr.parent_id, pr.stg_source_id
    FROM pr
    WHERE NOT EXISTS (SELECT 1 FROM pr as pr2 WHERE 
    pr2.stg_source_id = pr.parent_id)
  union all 
  Select a.parent_id, b.stg_source_id
  from CTE a inner join pr b on a.stg_source_id=b.parent_id
)Select parent_id, LISTAGG(stg_source_id, ',') WITHIN GROUP(ORDER BY stg_source_id) AS child
from CTE
GROUP BY parent_id;

It will aggregate stg_source_id into an array