By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production | Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 |
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production | 0 |
CREATE TABLE view_TABLE (date_column, user_1,ip, in_ip,out_ip,session_1,activity) AS
SELECT '2023-02-09T15:06', 'X',123,123,nULL,1,'IN' FROM DUAL UNION ALL
SELECT '2023-02-09T15:08' ,'X',123,NULL,123,1,'OUT' FROM DUAL UNION ALL
SELECT '2023-02-08T16:08' ,'Y',143,NULL,143,2,'OUT' FROM DUAL UNION ALL
SELECT '2023-02-08T16:04' ,'Y',153,153,NULL,2,'IN' FROM DUAL UNION ALL
SELECT '2023-02-08T12:45' ,'X',163,163,NULL,3,'IN' FROM DUAL UNION ALL
SELECT '2023-02-08T12:48' ,'X',163,NULL,163,3,'OUT' FROM DUAL;
6 rows affected
select * from view_table
| DATE_COLUMN | USER_1 | IP | IN_IP | OUT_IP | SESSION_1 | ACTIVITY |
|---|---|---|---|---|---|---|
| 2023-02-09T15:06 | X | 123 | 123 | null | 1 | IN |
| 2023-02-09T15:08 | X | 123 | null | 123 | 1 | OUT |
| 2023-02-08T16:08 | Y | 143 | null | 143 | 2 | OUT |
| 2023-02-08T16:04 | Y | 153 | 153 | null | 2 | IN |
| 2023-02-08T12:45 | X | 163 | 163 | null | 3 | IN |
| 2023-02-08T12:48 | X | 163 | null | 163 | 3 | OUT |
select
USER_1,
IP,
max(case when activity = 'IN' then DATE_COLUMN end) IN_1,
max(case when activity = 'OUT' then DATE_COLUMN end) OUT_1
from view_table
group by USER_1, IP, SESSION_1
| USER_1 | IP | IN_1 | OUT_1 |
|---|---|---|---|
| X | 123 | 2023-02-09T15:06 | 2023-02-09T15:08 |
| Y | 143 | null | 2023-02-08T16:08 |
| Y | 153 | 2023-02-08T16:04 | null |
| X | 163 | 2023-02-08T12:45 | 2023-02-08T12:48 |