SQL:实现行列转换

前言

推荐篇文章:Hive行列转换

这里我们要讨论的是如何实现把多个字段及其对应的字段，同时转换成两个字段。即如下表所示：

要做的是把它转换成下列形式：

实现

这里先直接上代码：

#代码一：
SELECT  SPLIT(code_name,':')[0] AS code,SPLIT(code_name,':')[1] AS name FROM(
SELECT EXPLODE(SPLIT(arr,',')) as code_name FROM(
SELECT 
CONCAT_WS(',',
CONCAT_WS(':',code1,name1),
CONCAT_WS(':',code2,name2),
CONCAT_WS(':',code3,name3)) AS arr
FROM code_name));

#代码二：
# 2.1
create temporary table tmp as SELECT CONCAT_WS(',',CONCAT_WS(':',code1,name1),CONCAT_WS(':',code2,name2),CONCAT_WS(':',code3,name3)) AS arr
FROM code_name;
# 2.2
create temporary table tmp2 as 
SELECT EXPLODE(SPLIT(arr,',')) as codeName FROM tmp; 
#2.3
SELECT  SPLIT(codename,':')[0] AS code,SPLIT(codename,':')[1] AS name FROM tmp2;

首先说明一下，代码一，这种方式在Hive中运行的话会报错；我是在odps中运行成功的。感兴趣的话大家可以在其他数据库试试。下面着重介绍下代码二，这块代码在Hive中可以成功运行。
hive中无法像实现代码一那种嵌套查询的方式(也有可能是我没找到正确的方法)，否则会报FAILED: ParseException...这类的错误。为此，我特地创建了两个临时表tmp和tmp2(临时表创建语法:create temporary table tmp as select * from tab)，即上述代码中的2.1和2.2。
tmp的结果为：

这里我们把他们合并成了一条数据

1:John,2:Mary,3:Sam

tmp2的结果为：

1列3行的数据

1:John
2:Mary
3:Sam

然后用split()分割成数组，即代码2.3所示：

1       John
2       Mary
3       Sam

补充：

后来又测试了下用侧视图的方式，lateral view + explode，想知道这样会产生什么效果。

select code from tmp2 
lateral view explode(split(codename,':')) c as code;

结果如下：
全部变为同一个字段数据。

1
John
2
Mary
3
Sam