By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t
("Name" varchar2(4), "Date" timestamp)
;
INSERT ALL
INTO t ("Name", "Date")
VALUES ('John', '01-Sep-2022 1:00:00 ')
INTO t ("Name", "Date")
VALUES ('John', '01-Sep-2022 9:00:00 ')
INTO t ("Name", "Date")
VALUES ('John', '01-Sep-2022 13:00:00 ')
INTO t ("Name", "Date")
VALUES ('John', '01-Sep-2022 16:00:00 ')
INTO t ("Name", "Date")
VALUES ('John', '02-Sep-2022 1:00:00 ')
INTO t ("Name", "Date")
VALUES ('John', '02-Sep-2022 6:00:00 ')
INTO t ("Name", "Date")
VALUES ('John', '04-Sep-2022 5:00:00 ')
INTO t ("Name", "Date")
VALUES ('John', '01-Oct-2022 4:30:00 ')
INTO t ("Name", "Date")
VALUES ('John', '01-Oct-2022 7:00:00 ')
INTO t ("Name", "Date")
VALUES ('Ali', '01-Sep-2022 10:00:00 ')
INTO t ("Name", "Date")
VALUES ('Ali', '04-Sep-2022 5:00:00 ')
SELECT * FROM dual
;
11 rows affected
select *
from t
| Name | Date |
|---|---|
| John | 01-SEP-22 01.00.00.000000 |
| John | 01-SEP-22 09.00.00.000000 |
| John | 01-SEP-22 13.00.00.000000 |
| John | 01-SEP-22 16.00.00.000000 |
| John | 02-SEP-22 01.00.00.000000 |
| John | 02-SEP-22 06.00.00.000000 |
| John | 04-SEP-22 05.00.00.000000 |
| John | 01-OCT-22 04.30.00.000000 |
| John | 01-OCT-22 07.00.00.000000 |
| Ali | 01-SEP-22 10.00.00.000000 |
| Ali | 04-SEP-22 05.00.00.000000 |
select *
from
(
select "Name" as "Name"
,to_char("Date", 'hh24:mi') as "Time"
,to_date(to_char("Date", 'DD-mon-YYYY')) as "Date"
,row_number() over(partition by "Name", to_char("Date", 'DD-mon-YYYY') order by to_char("Date", 'hh24:mi')) as rn
from t
) t
pivot(max("Time") for rn in('1' as Time1, '2' as Time2, '3' as Time3, '4' as Time4)) p
order by "Name" desc, "Date"
| Name | Date | TIME1 | TIME2 | TIME3 | TIME4 |
|---|---|---|---|---|---|
| John | 01-SEP-22 | 01:00 | 09:00 | 13:00 | 16:00 |
| John | 02-SEP-22 | 01:00 | 06:00 | null | null |
| John | 04-SEP-22 | 05:00 | null | null | null |
| John | 01-OCT-22 | 04:30 | 07:00 | null | null |
| Ali | 01-SEP-22 | 10:00 | null | null | null |
| Ali | 04-SEP-22 | 05:00 | null | null | null |