add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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