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 |