Concatenate all varchar values of a column above a row in oracle

I have a use case wherein there is a column which stores some characters, flg column and a category column.
Based on flg, if flg is 1 then for ith row the res1 should be concatenation of characters (concatenated by ''+'') of rows above the ith row and the ith row Column1 value. "Above ith row" meaning SEQNCE<SEQNCE(i).

If flg is zero then res1=Column1. This concatenation of rows above a row is limited only until category is same, if Category changes(eg. Category=2 Column1='I' is a new set) it should start from that row the process of concatenation.

For eg. below is the table named ''dmy_tbl''

LISTAGG is one such option to concatenate strings group by other column & LAG is option for fetching rows above a row, I have tried below query but it does not yield expected result. Also I'm not able to make a decision on how to partition on category. Any suggestion for this use case is highly welcomed.

select column1,catg,flg,SEQNCE,
case when ((flg=1 and res is null) or flg=0) then column1
else column1||'+'||res end as result
from
(select column1,catg,flg,SEQNCE,
case when flg=1 then lag(column1) 
over(order by catg,SEQNCE asc) else column1 end as res 
FROM dmy_tbl
order by catg,SEQNCE asc);

The inner query, res column is only fetching one row above, not sure on how to fit in LISTAGG in the inner query to fetch all above rows

You can use a MODEL clause:

SELECT column1,
       indicator,
       seqnce,
       category,
       CASE indicator
       WHEN 0
       THEN column1
       ELSE res
       END AS res1
FROM   dmy_tbl
MODEL
  PARTITION BY (category)
  DIMENSION BY (seqnce)
  MEASURES (
    column1,
    indicator,
    CAST(NULL AS VARCHAR2(4000)) AS res
  )  
  RULES (
    res[1] = column1[1],
    res[seqnce>1] = res[cv()-1] || '+' || column1[cv()]
  );

Which, for the sample data:

CREATE TABLE dmy_tbl (column1, indicator, seqnce, category) AS
SELECT 'A', 1, 1, 1 FROM DUAL UNION ALL
SELECT 'B', 0, 2, 1 FROM DUAL UNION ALL
SELECT 'C', 0, 3, 1 FROM DUAL UNION ALL
SELECT 'D', 1, 4, 1 FROM DUAL UNION ALL
SELECT 'E', 1, 5, 1 FROM DUAL UNION ALL
SELECT 'F', 1, 6, 1 FROM DUAL UNION ALL
SELECT 'G', 0, 7, 1 FROM DUAL UNION ALL
SELECT 'H', 1, 8, 1 FROM DUAL UNION ALL
SELECT 'I', 1, 1, 2 FROM DUAL UNION ALL
SELECT 'J', 0, 2, 2 FROM DUAL UNION ALL
SELECT 'K', 1, 3, 2 FROM DUAL UNION ALL
SELECT 'L', 1, 4, 2 FROM DUAL;

Outputs:

fiddle

From Oracle 12, you can use MATCH_RECOGNIZE and LISTAGG:

SELECT MAX(CASE rn WHEN 1 THEN column1 END) AS column1,
       MAX(CASE rn WHEN 1 THEN indicator END) AS indicator,
       MAX(CASE rn WHEN 1 THEN seqnce END) AS seqnce,
       category,
       LISTAGG(column1, '+') WITHIN GROUP (ORDER BY column1) AS res1
FROM   dmy_tbl
MATCH_RECOGNIZE(
  PARTITION BY category
  ORDER BY seqnce DESC
  MEASURES
    COUNT(*) AS rn,
    MATCH_NUMBER() AS mn
  ALL ROWS PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN ( zero | one any_row* )
  DEFINE
    zero AS indicator = 0,
    one  AS indicator = 1
)
GROUP BY category, mn
ORDER BY category, seqnce;

Which, for the sample data:

CREATE TABLE dmy_tbl (column1, indicator, seqnce, category) AS
SELECT 'A', 1, 1, 1 FROM DUAL UNION ALL
SELECT 'B', 0, 2, 1 FROM DUAL UNION ALL
SELECT 'C', 0, 3, 1 FROM DUAL UNION ALL
SELECT 'D', 1, 4, 1 FROM DUAL UNION ALL
SELECT 'E', 1, 5, 1 FROM DUAL UNION ALL
SELECT 'F', 1, 6, 1 FROM DUAL UNION ALL
SELECT 'G', 0, 7, 1 FROM DUAL UNION ALL
SELECT 'H', 1, 8, 1 FROM DUAL UNION ALL
SELECT 'I', 1, 1, 2 FROM DUAL UNION ALL
SELECT 'J', 0, 2, 2 FROM DUAL UNION ALL
SELECT 'K', 1, 3, 2 FROM DUAL UNION ALL
SELECT 'L', 1, 4, 2 FROM DUAL;

Outputs:

fiddle

If you don't care whether PL/SQL is involved, a simple function might do the job.

SQL> CREATE OR REPLACE FUNCTION f_res (par_category IN NUMBER, par_seqnce NUMBER)
2     RETURN VARCHAR2
3  IS
4     l_row   test%ROWTYPE;
5     retval  VARCHAR2 (30);
6  BEGIN
7     SELECT *
8       INTO l_row
9       FROM test
10      WHERE     category = par_category
11            AND seqnce = par_seqnce;
12
13     IF l_row.flg = 0
14     THEN
15        retval := l_row.column1;
16     ELSE
17        SELECT LISTAGG (column1, '+') WITHIN GROUP (ORDER BY seqnce)
18          INTO retval
19          FROM test
20         WHERE     category = par_category
21               AND seqnce <= par_seqnce;
22     END IF;
23
24     RETURN retval;
25  END;
26  /
Function created.

Testing:

SQL>   SELECT t.*, f_res (t.category, t.seqnce) res1
2      FROM test t
3  ORDER BY t.category, t.seqnce;
C     SEQNCE        FLG   CATEGORY RES1
- ---------- ---------- ---------- ------------------------------
A          1          1          1 A
B          2          0          1 B
C          3          0          1 C
D          4          1          1 A+B+C+D
E          5          1          1 A+B+C+D+E
F          6          1          1 A+B+C+D+E+F
G          7          0          1 G
H          8          1          1 A+B+C+D+E+F+G+H
I          1          1          2 I
J          2          0          2 J
K          3          1          2 I+J+K
L          4          1          2 I+J+K+L
12 rows selected.
SQL>