By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (csf, id, seqNum, data, rs_id) AS
SELECT 1, 1, 1, 'ABC', '01' FROM DUAL UNION ALL
SELECT 1, 2, 2, 'DEF', '02' FROM DUAL UNION ALL
SELECT 1, 3, 3, 'GHI', '03' FROM DUAL UNION ALL
SELECT 1, 4, 4, 'JKL', '04' FROM DUAL UNION ALL
SELECT 0, 5, 5, 'MNO', '05' FROM DUAL UNION ALL
SELECT 0, 5, 1, 'ABC', '06' FROM DUAL UNION ALL
SELECT 1, 6, 1, 'ABC', '07' FROM DUAL UNION ALL
SELECT 1, 7, 2, 'DEF', '08' FROM DUAL UNION ALL
SELECT 1, 8, 3, 'GHI', '09' FROM DUAL UNION ALL
SELECT 1, 8, 4, 'JKL', '10' FROM DUAL UNION ALL
SELECT 0, 9, 5, 'MNO', '11' FROM DUAL;
11 rows affected
SELECT MIN(last_csf) AS csf,
MIN(first_id) AS id,
MIN(last_seqnum) AS seqnum,
LISTAGG(data) WITHIN GROUP (ORDER BY seqnum) AS data,
MIN(last_rs_id) AS rs_id
FROM table_name
MATCH_RECOGNIZE(
ORDER BY id, seqnum
MEASURES
LAST(csf) AS last_csf,
FIRST(id) AS first_id,
LAST(seqNum) AS last_seqnum,
LAST(rs_id) AS last_rs_id,
MATCH_NUMBER() AS mno
ALL ROWS PER MATCH
PATTERN (one_csf* zero_csf)
DEFINE
one_csf AS csf = 1,
zero_csf AS csf = 0
)
GROUP BY mno
| CSF | ID | SEQNUM | DATA | RS_ID |
|---|---|---|---|---|
| 0 | 1 | 1 | ABCABCDEFGHIJKL | 01 |
| 0 | 5 | 5 | MNO | 05 |
| 0 | 6 | 1 | ABCDEFGHIJKLMNO | 07 |