Oracle 21c

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

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

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