Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE GLOBAL TEMPORARY TABLE my_gtt (
  id         NUMBER
 ,dates      VARCHAR2(200)
 ,is_active  NUMBER
) ON COMMIT PRESERVE ROWS;
BEGIN
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 15:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 10:00 - 13/07/2023 13:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 13:00 - 13/07/2023 15:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 10:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 0);
END;
/
1 rows affected

SELECT id,
       inactive,
       active
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY id, is_active ORDER BY dates) AS rn
  FROM   my_gtt t
)
PIVOT ( MAX(dates) FOR is_active IN (0 AS inactive, 1 AS active) )


ID
INACTIVE
ACTIVE


718842
13/07/2023 06:00 - 13/07/2023 08:00
13/07/2023 06:00 - 13/07/2023 08:00

718842
13/07/2023 08:00 - 13/07/2023 15:00
13/07/2023 08:00 - 13/07/2023 10:00

718842
13/07/2023 15:00 - 13/07/2023 17:00
13/07/2023 10:00 - 13/07/2023 13:00

718842
null
13/07/2023 13:00 - 13/07/2023 15:00

718842
null
13/07/2023 15:00 - 13/07/2023 17:00

718844
13/07/2023 06:00 - 13/07/2023 18:00
13/07/2023 06:00 - 13/07/2023 18:00

718844
13/07/2023 18:00 - 13/07/2023 19:00
13/07/2023 18:00 - 13/07/2023 19:00

ID	INACTIVE	ACTIVE
718842	13/07/2023 06:00 - 13/07/2023 08:00	13/07/2023 06:00 - 13/07/2023 08:00
718842	13/07/2023 08:00 - 13/07/2023 15:00	13/07/2023 08:00 - 13/07/2023 10:00
718842	13/07/2023 15:00 - 13/07/2023 17:00	13/07/2023 10:00 - 13/07/2023 13:00
718842	null	13/07/2023 13:00 - 13/07/2023 15:00
718842	null	13/07/2023 15:00 - 13/07/2023 17:00
718844	13/07/2023 06:00 - 13/07/2023 18:00	13/07/2023 06:00 - 13/07/2023 18:00
718844	13/07/2023 18:00 - 13/07/2023 19:00	13/07/2023 18:00 - 13/07/2023 19:00