hive行转列和列转行

创建测试表：
create table stu1( 
name         string comment '姓名',
chinese      string comment '语文',
math         string comment '数学',
english      string comment '英语'
)comment '学生成绩表1' row format delimited fields terminated by ',';

create table stu2( 
name          string comment '姓名',
subject       string comment '科目',
performance   string comment '成绩'
)comment '学生成绩表2' row format delimited fields terminated by ',';

加载数据：
load data local inpath '/home/Devin/stu2.txt' into table stu2;

hive (pingan)> select * from stu1;
OK
stu1.name       stu1.chinese    stu1.math       stu1.english
小王    				75      		73      		86
小李   			    98      		87     		    89
小刘    				76      		87      		65
Time taken: 0.355 seconds, Fetched: 3 row(s)

hive (pingan)> select * from stu2;                                           
OK
stu2.name       stu2.suject     stu2.performance
小王    			chinese 			75
小李    			chinese 			98
小刘    			chinese			    76
小王    			math    			73
小李    			math    			87
小刘    			math    			87
小王    			english 			86
小李    			english 			89
小刘    			english 			65
Time taken: 0.078 seconds, Fetched: 9 row(s)

行转列：stu1数据格式转为stu2数据格式
select name,
       t.subject,
       t.performance
    from stu1 
    lateral view explode(
        str_to_map(
            concat('chinese=',chinese,'&math=',math,'&english=',english)
        ,'&','=')
    ) t as subject,performance;
name    t.subject       t.performance
小王    math    73
小王    english 86
小王    chinese 75
小李    math    87
小李    english 89
小李    chinese 98
小刘    math    87
小刘    english 65
小刘    chinese 76
Time taken: 89.781 seconds, Fetched: 9 row(s)
列转行：stu2数据格式转为stu1数据格式
select name,
       sum(case when subject = 'chinese' then performance end) as chinese,
       sum(case when subject = 'math' then performance end) as math,
       sum(case when subject = 'english' then performance end) as english 
	from stu2 
	group by name;
name    chinese math    english
小刘    76.0    87.0    65.0
小李    98.0    87.0    89.0
小王    75.0    73.0    86.0
Time taken: 88.087 seconds, Fetched: 3 row(s)

Lateral View用法与Hive UDTF explode:https://blog.csdn.net/oopsoom/article/details/26001307
hive中UDF、UDAF和UDTF详解:https://blog.csdn.net/zhaoli081223/article/details/46637517