HIVESQL-列转行&行转列(初级版）_hive sql行转列

一、列转行：

列数据：

转换后行数据：

方法：通过union all 实现

select userid, '语文' as subjectName ,chinese_score as socre  from score
union all 
select userid, '数学' as subjectName ,math_score as socre  from score
union all 
select userid, '英语' as subjectName ,english_score as socre  from score
union all 
select userid, '政治' as subjectName ,politics_score as socre  from score
1
2
3
4
5
6
7

二、行转列：

行数据：

转化后列数据：

方法一：case when … then …

select userid,
sum(case  when subjectName='语文' then score else 0 end )as '语文',
sum(case  when subjectName='数学' then score else 0 end )as '数学',
sum(case  when subjectName='英语' then score else 0 end )as '英语',
sum(case  when subjectName='政治' then score else 0 end )as '政治'
from score
group by userid;
1
2
3
4
5
6
7

方法二：sum(if())

select userid,
sum(if(subjectName='语文' , score , 0 ))as '语文',
sum(if(subjectName='数学' , score , 0 ))as '数学',
sum(if(subjectName='英语' , score , 0 ))as '英语',
sum(if(subjectName='政治' , score , 0 ))as '政治'
from score
group by userid;
1
2
3
4
5
6
7

[参考文章]：
http://www.360doc.com/content/21/0614/01/37253146_981925362.shtml