Combining several rows based on a parameter in Oracle

I have a table like below:

I would like to be able to combine some of the rows (including aggregating O_ID's and replacing T_STOP's with the MAX) based on the parameter that given C_ID is the same and the T_STOP of one row is within 2 hours of the next T_START. So, given the above table, the result should be:

I figure there has to be some recursion involved, but I can't quite wrap my head around it.

From Oracle 12, you can use MATCH_RECOGNIZE for row-by-row pattern matching to find the groups of rows within 2-hours of each other; once you have found the groups you can aggregate:

SELECT c_id,
       LISTAGG(o_id, ',') WITHIN GROUP (ORDER BY o_id) AS o_ids,
       MIN(t_start) AS t_start,
       MAX(t_stop) AS t_stop
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY c_id
         ORDER BY o_id
         MEASURES
           MATCH_NUMBER() AS mn
         ALL ROWS PER MATCH
         PATTERN (first_row within_2_hours*)
         DEFINE
           within_2_hours AS t_start <= PREV(t_stop) + INTERVAL '2' HOUR
       )
GROUP BY c_id, mn

In Oracle 11, you can use analytic functions to find the groups:

SELECT c_id,
       LISTAGG(o_id, ',') WITHIN GROUP (ORDER BY o_id) AS o_ids,
       MIN(t_start) AS t_start,
       MAX(t_stop) AS t_stop
FROM   (
  SELECT c_id,
         o_id,
         t_start,
         t_stop,
         SUM(has_changed_group) OVER (PARTITION BY c_id ORDER BY o_id) AS mn
  FROM   (
    SELECT t.*,
           CASE
           WHEN t_start
                <= LAG(t_stop) OVER (PARTITION BY c_id ORDER BY o_id) + INTERVAL '2' HOUR
           THEN 0
           ELSE 1
           END AS has_changed_group
    FROM   table_name t
  )
)
GROUP BY c_id, mn

Which, for the sample data:

CREATE TABLE table_name (C_ID, O_ID, T_START, T_STOP) AS
SELECT 1, 1, DATE '2025-05-07' + INTERVAL '12:06' HOUR TO MINUTE, DATE '2025-05-08' + INTERVAL '11:09' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, 2, DATE '2025-05-08' + INTERVAL '11:24' HOUR TO MINUTE, DATE '2025-05-10' + INTERVAL '11:24' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, 3, DATE '2025-05-10' + INTERVAL '15:41' HOUR TO MINUTE, DATE '2025-05-11' + INTERVAL '12:38' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, 4, DATE '2025-05-14' + INTERVAL '11:24' HOUR TO MINUTE, DATE '2025-05-16' + INTERVAL '07:20' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, 5, DATE '2025-05-16' + INTERVAL '08:50' HOUR TO MINUTE, DATE '2025-05-18' + INTERVAL '08:50' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, 6, DATE '2025-05-18' + INTERVAL '09:24' HOUR TO MINUTE, DATE '2025-05-20' + INTERVAL '09:24' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, 1, DATE '2025-05-10' + INTERVAL '11:42' HOUR TO MINUTE, DATE '2025-05-12' + INTERVAL '11:42' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, 2, DATE '2025-05-12' + INTERVAL '11:44' HOUR TO MINUTE, DATE '2025-05-14' + INTERVAL '11:43' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, 3, DATE '2025-05-18' + INTERVAL '00:02' HOUR TO MINUTE, DATE '2025-05-19' + INTERVAL '02:54' HOUR TO MINUTE FROM DUAL;

Both output:

fiddle