下面是获取所需输出的sql。

select 'roll_1' as subject,sum(case when subject='MATHS' then roll_1 else 0 end) maths,sum(case when subject='ENGLISH' then roll_1 else 0 end) english from your_table_2
union all
select 'roll_2' as subject,sum(case when subject='MATHS' then roll_2 else 0 end) maths,sum(case when subject='ENGLISH' then roll_2 else 0 end) english from your_table_2
union all
select 'roll_3' as subject,sum(case when subject='MATHS' then roll_3 else 0 end) maths,sum(case when subject='ENGLISH' then roll_3 else 0 end) english from your_table_2
union all
select 'roll_4' as subject,sum(case when subject='MATHS' then roll_4 else 0 end) maths,sum(case when subject='ENGLISH' then roll_4 else 0 end) english from your_table_2
union all
select 'roll_5' as subject,sum(case when subject='MATHS' then roll_5 else 0 end) maths,sum(case when subject='ENGLISH' then roll_5 else 0 end) english from your_table_2

这是我能做的最接近泛型的了，随着新列的添加，您只需在map（）内的concat（）中进行更改：

select pos1+1 AS rollnum,mat,eng from(
  select collect_list(a.group_map['MATHS']) as MATHS,
         collect_list(a.group_map['ENGLISH']) as ENGLISH,
         from ( select map(SUBJECT,CONCAT(ROLL1,',',ROLL2,',',ROLL3,',',ROLL4,',',
                                                   ROLL5,',',ROLL6)) as group_map
                                                    from db_name.tbl_name) a) b
         lateral view posexplode(split(b.MATHS[0],',')) MATHS AS pos1,mat
         lateral view posexplode(split(b.ENGLISH[0],',')) ENGLISH AS pos2,eng

         WHERE pos1=pos2

虽然这可能会影响效率。