MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t as

select 1 as id, 1 as col1, 2 as col2, 6 as col3 union all

select 1, 1, 2, 6 union all

select 2, 7, 4, 8 union all

select 3, 4, 1, 5

select max(case when id = 1 then col end) as id_1,

max(case when id = 2 then col end) as id_2,

max(case when id = 3 then col end) as id_3

from ((select id, col1 as col, 1 as which from t) union all

(select id, col2 as col, 2 as which from t) union all

(select id, col3 as col, 3 as which from t)

) ic

group by which;