Running STRING_AGG until previous depth in a given window

Question

I have data this way

ImplementionNumber     Depth    Value
    1                    0      ABC
    1                    1      PQR
    1                    1      PQR
    1                    1      RST
    1                    2      QWE
    1                    2      ERT
    1                    2      THU
    1                    2      YWR
    1                    2      OPL
    1                    3      UJK
    1                    3      LKI
    1                    3      GYU
    1                    4      IKI
    1                    5      TYH
    2                    0      GYH
    2                    1      PLO
    2                    1      DFG
    2                    2      QER
    2                    3      TYE
    2                    3      EDG
    2                    3      BHK

My partition window is ImplementionNumber Now within this partition, for any given Depth, I need to have a running STRING_AGG of all the distinct Value until previous Depth

Output expected is something like this

ImplementionNumber     Depth    Value    Output
    1                    0      ABC      NULL
    1                    1      PQR      ABC
    1                    1      PQR      ABC
    1                    1      RST      ABC
    1                    2      QWE      ABC,PQR,RST
    1                    2      ERT      ABC,PQR,RST
    1                    2      THU      ABC,PQR,RST
    1                    2      YWR      ABC,PQR,RST
    1                    2      OPL      ABC,PQR,RST
    1                    3      UJK      ABC,PQR,RST,QWE,ERT,THU,YWR,OPL
    1                    3      LKI      ABC,PQR,RST,QWE,ERT,THU,YWR,OPL
    1                    3      GYU      ABC,PQR,RST,QWE,ERT,THU,YWR,OPL
    1                    4      IKI      ABC,PQR,RST,QWE,ERT,THU,YWR,OPL,UJK,LKI,GYU
    1                    5      TYH      ABC,PQR,RST,QWE,ERT,THU,YWR,OPL,UJK,LKI,GYU,IKI
    2                    0      GYH      NULL
    2                    1      PLO      GYH
    2                    1      DFG      GYH
    2                    2      QER      GYH,PLO,DFG
    2                    3      TYE      GYH,PLO,DFG,QER
    2                    3      EDG      GYH,PLO,DFG,QER
    2                    3      BHK      GYH,PLO,DFG,QER

user_5221944 · Answer

Con sider below

select * except(OutputArr),
  ( select string_agg(Value, ',' order by Depth)
    from (
      select distinct as struct Value, Depth
      from t.OutputArr
  )) Output
from (
  select *, array_agg(struct(Value, Depth)) over win OutputArr
  from your_table 
  window win as (
    partition by ImplementionNumber 
    order by Depth 
    range between unbounded preceding and 1 preceding
  )
) t

if applied to sample data in your question - output is

Running STRING_AGG until previous depth in a given window

1 Answers