Hive
1.行转列
select a.cust_id,b.cut_load,b.point
from(
select * from 表名
) a
LATERAL VIEW EXPLODE (map(
1,p1
,2,p2
))b as point,cut_load
1,p1 前面是转成 point字段下的值,p1 是获得原表字段的值传递给cut_load字段的值
2.列转行
两种方法
select name
,info['语文'] as Chinese
,info['数学'] as Math
,info['英语'] as English
from (select name,str_to_map(concat_ws(',',collect_set(concat_ws(':',subject,cast(score as string))))) as info
from test
group by name
) a
select name
,max(case when subject = '语文' then score else 0 end) as Chinese
,max(case when subject = '数学' then score else 0 end) as Math
,max(case when subject = '英语' then score else 0 end) as English
from test
group by name
Oracle
1.行转列
select cust_id,point,cut_load
from
(
select * from 表名
)
unpivot
(
cut_load for point in
(
p1,p2
)
)
2.列转行
select name
,max(case when subject = '语文' then score else 0 end) as Chinese
,max(case when subject = '数学' then score else 0 end) as Math
,max(case when subject = '英语' then score else 0 end) as English
from test
group by name