hivesql行转列和列转行_hive sql 列转行

对于行专列和列转行，有两种不同的理解，这里将两中不同的理解都整理出来，供大家参考。

1. 字段值不发生改变，只是改变行列的分布

1.1. 行转列

1. 有如下的一张成绩表tab_scores，每个人的成绩是按行存储的，要求每个人的成绩按列存储，即为行转列

2. 转换sql如下：

select name,
	sum(case subject when 'Math' then score else 0 end) as Math,
	sum(case subject when 'Chinese' then score else 0 end) as Chinese,
	sum(case subject when 'English' then score else 0 end) as English
from tab_scores
group by name
1
2
3
4
5
6

3. 查询结果如下：

1.2. 列转行

1. 数据如下

2. 转换sql如下：

select name,'Math',Math from tab_scores
union all
select name,'English',English from tab_scores
union all
select name,'Chinese',Chinese from tab_scores
1
2
3
4
5

3. 查询结果如下

2. 字段值进行分割，形成新的行或者列

2.1. 行转列：把多行转成一列（多行变一行）

1. 需要用的函数

• collect_set()/collect_list()
  将一堆数据收集成数据集
  collect_set( )函数只接受基本数据类型，作用是对参数字段进行去重汇总，返回array类型字段;
  collect_list()函数和collect_set( )作用一样，只是前者不去重,后者去重。
• concat_ws()
  concat_ws(‘,’,Array)，第一个参数是连接符，第二个是数据集、将一个数据集的数据按照给定的符号连接成字符串

2. 数据如下,，数据表为hobbyInfo

3. 转换sql：

select name,concat_ws(',',collect_set(hobby)) as hobby 
from hobbyInfo group by name
1
2

4. 查询结果：

2.2. 列转行，把一列转成多行

1. 需要用到的函数

• split():
  将一个字符串按照指定字符分割，结果为一个array；
  split(str,‘,’)，将字符串str按逗号进行分割得到一个数数组
• explode():
  炸裂函数，将一列复杂的array或者map拆分为多行,它的参数必须为map或array；
  explode(arr) ,将数组arr拆分为多行的数据
• lateral view:
  lateral view udtf(字段名)表别名/表临时名as列别名/列临时名。lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateralview再把结果组合，产生一个支持别名表的虚拟表。

2. 数据如下：表hobbyInfo

3. 转换sql

select a.name,tabHobby.colHobby as hobby from hobbyInfo a
lateral view explode(split(a.hobby,',')) tabHobby as colHobby
-- tabHobby是侧视图的别名，colHobby是解析出来的字段名，#注意，as不能省略
1
2
3

4. 查询结果如下