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