Using window function in redshift to aggregate conditionally

I have a table with following data:

Link to test data: http://sqlfiddle.com/#!15/dce01/1/0

I want to aggregate the items column (using listagg) for each group in gid in sequence as specified by seq column based on the condition that aggregation ends when pid becomes 0 again for a group.

i.e. for group g1, there would be 2 aggregations; 1 for seq 1-3 and another for sequence 4-6; since for group g1, the pid becomes 0 for seq 4.

I expect the result for the given example to be as follows (Please note that seq in result is the min value of seq for the group where the pid becomes 0):

Answer

I understand your question as a gaps and island problem, where you want to group together adjacent rows having the same gid untiil a pid having value 0 is met.

Here is one way to solve it using a window sum to define the groups: basically, a new island starts everytime a pid of 0 is met. The rest is just aggregation:

select
    gid,
    min(seq) seq,
    listagg(items, ',') within group(order by seq) items
from (
    select 
        t.*,
        sum(case when pid = 0 then 1 else 0 end) over(partition by gid order by seq) grp
    from mytable t
) t
group by gid, grp
order by gid, grp