Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table your_table (time_col, length_col, name_col) as
select '00:01:00', 2, 'a' from dual
union all
select '00:11:22', 2, 'a' from dual 
union all
select '01:01:00', 45, 'a' from dual
union all
select '00:23:00', 3, 'b' from dual;
4 rows affected

select time_col, length_col, name_col,
  dense_rank() over (partition by name_col order by time_col) as rnk
from your_table;


TIME_COL
LENGTH_COL
NAME_COL
RNK


00:01:00
2
a
1

00:11:22
2
a
2

01:01:00
45
a
3

00:23:00
3
b
1


select *
from (
  select time_col, length_col, name_col,
    dense_rank() over (partition by name_col order by time_col) as rnk
  from your_table
)
pivot (
  max(time_col) as time_col, max(length_col) as length_col
  for name_col in ('a' as a, 'b' as b)
);


RNK
A_TIME_COL
A_LENGTH_COL
B_TIME_COL
B_LENGTH_COL


2
00:11:22
2
null
null

3
01:01:00
45
null
null

1
00:01:00
2
00:23:00
3


select a_time_col, a_length_col, b_time_col, b_length_col
from (
  select time_col, length_col, name_col,
    dense_rank() over (partition by name_col order by time_col) as rnk
  from your_table
)
pivot (
  max(time_col) as time_col, max(length_col) as length_col
  for name_col in ('a' as a, 'b' as b)
);


A_TIME_COL
A_LENGTH_COL
B_TIME_COL
B_LENGTH_COL


00:11:22
2
null
null

01:01:00
45
null
null

00:01:00
2
00:23:00
3


select
  max(case when name_col = 'a' then time_col end) as time_col_a,
  max(case when name_col = 'a' then length_col end) as length_col_a,
  max(case when name_col = 'b' then time_col end) as time_col_b,
  max(case when name_col = 'b' then length_col end) as length_col_b
from (
  select time_col, length_col, name_col,
    dense_rank() over (partition by name_col order by time_col) as rnk
  from your_table
)
group by rnk
order by rnk;


TIME_COL_A
LENGTH_COL_A
TIME_COL_B
LENGTH_COL_B


00:01:00
2
00:23:00
3

00:11:22
2
null
null

01:01:00
45
null
null


WITH CTE1 AS 
(SELECT T.*, ROW_NUMBER() OVER (ORDER BY LENGTH, TIME) AS RN FROM YOUR_TABLE WHERE NAME = 'a'),
CTE2 AS 
(SELECT T.*, ROW_NUMBER() OVER (ORDER BY LENGTH, TIME) AS RN FROM YOUR_TABLE WHERE NAME = 'b')
SELECT A.TIME, A.LENGTH, B.TIME, B.LENGTH
FROM CTE1 A FULL OUTER JOIN CTE2 B
ON A.RN = B.RN
ORA-00904: "T": invalid identifier
RNK	A_TIME_COL	A_LENGTH_COL	B_TIME_COL	B_LENGTH_COL
2	00:11:22	2	null	null
3	01:01:00	45	null	null
1	00:01:00	2	00:23:00	3