By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH:MI:SS';
CREATE TABLE tab
AS
SELECT CAST('2021-12-03 04:03:45' AS DATE) AS tmp,'ID1' AS id,'O' AS value FROM dual
UNION ALL SELECT CAST('2021-12-03 04:03:46' AS DATE),'ID1','P' FROM dual
UNION ALL SELECT CAST('2021-12-03 04:03:47' AS DATE),'ID1','Q' FROM dual
UNION ALL SELECT CAST('2021-12-03 04:03:48' AS DATE),'ID1','R' FROM dual
UNION ALL SELECT CAST('2021-12-03 04:03:49' AS DATE),'ID1',NULL FROM dual
UNION ALL SELECT CAST('2021-12-03 04:03:50' AS DATE),'ID1','S' FROM dual
UNION ALL SELECT CAST('2021-12-03 04:03:51' AS DATE),'ID1','T' FROM dual
UNION ALL SELECT CAST('2021-12-04 11:09:03' AS DATE),'ID2','A' FROM dual
UNION ALL SELECT CAST('2021-12-04 11:09:04' AS DATE),'ID2','B' FROM dual
UNION ALL SELECT CAST('2021-12-04 11:09:05' AS DATE),'ID2','C' FROM dual
UNION ALL SELECT CAST('2021-12-04 11:09:06' AS DATE),'ID2','D' FROM dual
11 rows affected
SELECT tab.*,
COALESCE(FIRST_VALUE(CASE WHEN VALUE IS NULL THEN tmp END) IGNORE NULLS
OVER(PARTITION BY ID ORDER BY TMP
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
, MAX(tmp) OVER(PARTITION BY ID)) AS next_tmp
FROM tab
| TMP | ID | VALUE | NEXT_TMP |
|---|---|---|---|
| 2021-12-03 04:03:45 | ID1 | O | 2021-12-03 04:03:49 |
| 2021-12-03 04:03:46 | ID1 | P | 2021-12-03 04:03:49 |
| 2021-12-03 04:03:47 | ID1 | Q | 2021-12-03 04:03:49 |
| 2021-12-03 04:03:48 | ID1 | R | 2021-12-03 04:03:49 |
| 2021-12-03 04:03:49 | ID1 | null | 2021-12-03 04:03:49 |
| 2021-12-03 04:03:50 | ID1 | S | 2021-12-03 04:03:51 |
| 2021-12-03 04:03:51 | ID1 | T | 2021-12-03 04:03:51 |
| 2021-12-04 11:09:03 | ID2 | A | 2021-12-04 11:09:06 |
| 2021-12-04 11:09:04 | ID2 | B | 2021-12-04 11:09:06 |
| 2021-12-04 11:09:05 | ID2 | C | 2021-12-04 11:09:06 |
| 2021-12-04 11:09:06 | ID2 | D | 2021-12-04 11:09:06 |
WITH cte AS (
SELECT tab.*,
COALESCE(FIRST_VALUE(CASE WHEN VALUE IS NULL THEN tmp END) IGNORE NULLS
OVER(PARTITION BY ID ORDER BY TMP
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
,MAX(tmp) OVER(PARTITION BY ID)) AS next_tmp
FROM tab
)
SELECT c1.tmp, c1.id, c1.value,
LISTAGG(c2.value, ',') WITHIN GROUP(ORDER BY c2.tmp) AS list
FROM cte c1
LEFT JOIN cte c2
ON c1.ID = c2.ID
AND c2.tmp <= c1.next_tmp
AND c1.tmp < c2.tmp
GROUP BY c1.tmp, c1.id, c1.value
ORDER BY c1.ID, c1.tmp;
| TMP | ID | VALUE | LIST |
|---|---|---|---|
| 2021-12-03 04:03:45 | ID1 | O | P,Q,R |
| 2021-12-03 04:03:46 | ID1 | P | Q,R |
| 2021-12-03 04:03:47 | ID1 | Q | R |
| 2021-12-03 04:03:48 | ID1 | R | null |
| 2021-12-03 04:03:49 | ID1 | null | null |
| 2021-12-03 04:03:50 | ID1 | S | T |
| 2021-12-03 04:03:51 | ID1 | T | null |
| 2021-12-04 11:09:03 | ID2 | A | B,C,D |
| 2021-12-04 11:09:04 | ID2 | B | C,D |
| 2021-12-04 11:09:05 | ID2 | C | D |
| 2021-12-04 11:09:06 | ID2 | D | null |