行
<=> 列
行->列
使用 case when;group by + sum
sql
行->列
使用 case when;group by + sum
![](https://i-blog.csdnimg.cn/blog_migrate/3e9911e827bab61d3d43b88cc3018cad.png)
![](https://i-blog.csdnimg.cn/blog_migrate/ba5e5ca6cf1d0026b82d88946cea92b8.png)
sql
select id,
sum(case when course="java" then 1 else 0 end) as java,
sum(case when course="hadoop" then 1 else 0 end) as hadoop,
sum(case when course="hive" then 1 else 0 end) as hive,
sum(case when course="hbase" then 1 else 0 end) as hbase,
sum(case when course="spark" then 1 else 0 end) as spark,
sum(case when course="flink" then 1 else 0 end) as flink,
sum(case when course="kafka" then 1 else 0 end) as kafka
from rowline1 group by id;
行->列 多行一列
数据
a,b,2
a,b,1
a,b,3
c,d,6
c,d,8
c,d,8
实现效果:
id1 id2 flag
a b 2|1|3
c d 6|8
表结构:
create table rowline2( id1 string, id2 string, flag int ) row format delimited fields terminated by ',';
导入数据
load data local inpath '/root/data/data2.dat' into table rowline2;
实现步骤:
1.第一步 将元素聚拢
2.第二步 将元素连接在一起
1)collect_set(去重集合) collect_list(不去重集合) sort_array(排序去重集合) 按照实现效果选择第一个集合
select id1, id2, collect_set(flag) flag from rowline2 group by id1, id2;
id1 id2 flag
a b [2,1,3]
c d [6,8]
select id1, id2, collect_list(flag) flag from rowline2 group by id1, id2;
id1 id2 flag
a b [2,1,3]
c d [6,8,8]
select id1, id2, sort_array(collect_set(flag)) flag from rowline2 group by id1, id2;
id1 id2 flag
a b [1,2,3]
c d [6,8]
2)元素连接在一起
select id1, id2, concat_ws("|", collect_set(cast(flag as string))) flag from rowline2 group by id1, id2;
id1 id2 flag
a b 2|1|3
c d 6|8
函数说明:
concat_ws
concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator.
Example:
> SELECT concat_ws('.', 'www', array('facebook', 'com')) FROM src LIMIT 1;
'www.facebook.com'
concat
concat(str1, str2, ... strN) - returns the concatenation of str1, str2, ... strN or concat(bin1, bin2, ... binN) - returns the concatenation of bytes in binary data bin1, bin2, ... binN
Returns NULL if any argument is NULL.
Example:
> SELECT concat('abc', 'def') FROM src LIMIT 1;
'abcdef'
列->行 一列多行
create table rowline3 as select id1, id2,
concat_ws("|", collect_set(cast (flag as string))) flag
from rowline2 group by id1, id2;
rowline3.id1 rowline3.id2 rowline3.flag
a b 2|1|3
c d 6|8
select id1, id2, newflag
from rowline3
lateral view explode(split(flag, "\\|")) t1 as newflag;
id1 id2 newflag
a b 2
a b 1
a b 3
c d 6
c d 8
行->列 多行多列
case when + sum + group by
行->列 多行一列
collect_set、collect_list、concat_ws、sort_array
列->行 一列多行
explode + lateral view