Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (id int, col_1 int, col_2 int, col_3 int, col_4 int,
  col_5 int, col_6 int);
insert into t values (1, 2, 5, 2, 9, 4, 7);
1 rows affected

insert into t values (2, 2, 4, 3, 5, 4, 7);
1 rows affected

insert into t values (3, 2, 2, 3, 5, 4, 8);
1 rows affected

with unpivoted as (
    select id, col_1 as col from t
    union all
    select id, col_2 as col from t
    union all
    select id, col_3 as col from t
    union all
    select id, col_4 as col from t
    union all
    select id, col_5 as col from t
    union all
    select id, col_6 as col from t
)
select id,
       max(case when rn = 1 then col end) col_1,
       max(case when rn = 2 then col end) col_2,
       max(case when rn = 3 then col end) col_3,
       max(case when rn = 4 then col end) col_4
from
(
  select id, col, row_number() over (partition by id order by col desc) rn
  from unpivoted
) dt
where rn <= 4
group by id;


ID
COL_1
COL_2
COL_3
COL_4


1
9
7
5
4

2
7
5
4
4

3
8
5
4
3


with unpivoted as (
    select id, col_1 as col from t
    union all
    select id, col_2 as col from t
    union all
    select id, col_3 as col from t
    union all
    select id, col_4 as col from t
    union all
    select id, col_5 as col from t
    union all
    select id, col_6 as col from t
)
select id,
       max(case when rn = 1 then col end) col_1,
       max(case when rn = 2 then col end) col_2,
       max(case when rn = 3 then col end) col_3,
       max(case when rn = 4 then col end) col_4
from
(
  select id, col, DENSE_RANK() over (partition by id order by col desc) rn
  from unpivoted
) dt
where rn <= 4
group by id;


ID
COL_1
COL_2
COL_3
COL_4


1
9
7
5
4

2
7
5
4
3

3
8
5
4
3