行转列，列转行

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