列转行（spark 与presto语法）

一、Presto 语法

原始数据：

期望数据：

代码：

SELECT info, value
FROM 
(	 
    select '张三'  as name,'18' as age,'男' as gender,'清华' as school
    union all
    select '李四'  as name,'18' as age,'男' as gender,'清华' as school
) as a
CROSS JOIN UNNEST(ARRAY['name','age','gender','school'], ARRAY[name,age,gender,school]) AS t (info, value)

二、Spark2.3

原始数据：

期望数据：

语法：

SELECT info, value
FROM 
(	 
    select '张三'  as name,'18' as age,'男' as gender,'清华' as school
    union all
    select '李四'  as name,'18' as age,'男' as gender,'清华' as school
) as a
LATERAL VIEW posexplode(ARRAY('name','age','gender','school')) f1 AS f11, info 
LATERAL VIEW posexplode(ARRAY(name,age,gender,school))         f2 AS f22, value
WHERE f11 = f22