如何在hiveql中将列和行作为标题

i86rm4rw  于 2021-06-25  发布在  Hive
关注(0)|答案(1)|浏览(337)

我有一个关于在hiveql中将列和行设置为标题的问题。因此,我的预期输出如下(表1):

但到目前为止我能做的就是这样(表2):

使用此查询:

SELECT
sum(case when grade ='A' and class = 'I' then 1 else 0 end) as 'Grade_A_I',
sum(case when grade ='B' and class = 'I' then 1 else 0 end) as 'Grade_B_I',
sum(case when grade ='C' and class = 'I' then 1 else 0 end) as 'Grade_C_I',
sum(case when grade ='A' and class = 'II' then 1 else 0 end) as 'Grade_A_II', etc...
FROM
grade_table
where
    .......

我在网上找不到这样做的参考资料,所以我只是想知道有没有什么方法可以实现表1而不是表2?
非常感谢大家的意见,提前谢谢!

kmynzznz

kmynzznz1#

这样行吗?

select 
case when class = 'I' then 'Class I'
when class = 'II' then 'Class II' end as class,
sum(case when grade ='A' then 1 else 0 end) as Grade_A,
sum(case when grade ='B' then 1 else 0 end) as Grade_B,
sum(case when grade ='C' then 1 else 0 end) as Grade_C
from 
grade_table
group by 1
union all
select 'Total' as class,
sum(case when grade ='A' then 1 else 0 end) as Grade_A,
sum(case when grade ='B' then 1 else 0 end) as Grade_B,
sum(case when grade ='C' then 1 else 0 end) as Grade_C
from 
grade_table

相关问题