hive关于行转列，列转行的使用

行转列：

定义：就是把形如

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

的数据转换为

id -------------name

1，2，3，4 --------- a

这里把四行的数据变成了由一行显示，这就是行转列。

首先要用到的是collect_set或者collect_list，前者去重，后者不去重，一般配合group使用，能把形如：

id --------- name

1 ---------a

2 ---------a

3 ---------a

4 ---------a

转换为

id --------- name

["1","2","3","4"]---------a

然后需要用到的是concat_ws函数，这个函数需要传递指定分隔符，以及字符串或者字符串数组。能起到把多个字符串拼接到一起的作用。

形如：

id --------- name

["1","2","3","4"]---------a

通过concat_ws(',',[1,2,3,4])转换为

id--------- name

1，2，3，4 --------- a

连起来就完成了行转列

一般sql为:

列转行

定义：就是把形如

id -------------name

1，2，3，4 --------- a

的数据转换为

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

这里把一行的数据变成了由四行显示，这就是列转行。

首先要用到split函数，这个行数可以把字符串按照指定的规则切分为字符串数组。

形如：

id --------- name

1，2，3，4 --------- a

通过split('1,2,3,4',',')转换为

id--------- name

["1","2","3","4"]---------a

然后是explode函数，这个行数可以把数组的每个元素转换为一行。

形如

id

["1","2","3","4"]

通过explode(["1","2","3","4"] )转换为

id

1

2

3

4

最后为了拼接其它字段，还需要用到lateral view把explode获得的行当虚拟表来用。

使用方法为 lateral view explode(split(["1","2","3","4"] , ',')) tmptable as new_id

其中new_id元素为转换为行后的字段名。

把以上综合起来就完成了列转行

一般sql为:

PIVOT 用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现

PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别(在数据库属性->选项->兼容级别改为 90 )

SQL2008 中可以直接使用

完整语法：

table_source

PIVOT(

聚合函数（value_column）

FOR pivot_column

IN(<column_list>)

)

View Code

UNPIVOT 用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现

完整语法：

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

姓名 数学 语文 姓名 张飞 赵云 庞统

张飞 6061数学 60 8099

赵云 8085 变成语文 61 8580

庞统 9980

而且第一行为列名，不是表中的数据，这样的话

也就是第一个表下面三行的数据，变成第二个表下面两行的数据

这样还是可以的

---