hive行转列

--转成map，再取值

WITH student AS 
( 
SELECT '张三' AS name ,'语文' AS class, 100 AS score FROM system.dual UNION ALL 
SELECT '张三' AS name ,'数学' AS class, 88 AS score FROM system.dual UNION ALL 
SELECT '李四' AS name ,'语文' AS class, 98 AS score FROM system.dual UNION ALL 
SELECT '李四' AS name ,'数学' AS class, 99 AS score FROM system.dual 
) 
WITH tb AS 
( 
SELECT 
name, 
str_to_map(CONCAT_WS(',',collect_list(concat(class,':',score))),',',':') as kv 
FROM STUDENT GROUP BY name 
) 
SELECT name AS name,map_values(kv)[0] AS '语文',map_values(kv)[1] AS '数学' FROM tb; 

--CASE WHEN 方式

WITH student AS 
( 
SELECT '张三' AS name ,'语文' AS class, 100 AS score FROM system.dual UNION ALL 
SELECT '张三' AS name ,'数学' AS class, 88 AS score FROM system.dual UNION ALL 
SELECT '李四' AS name ,'语文' AS class, 98 AS score FROM system.dual UNION ALL 
SELECT '李四' AS name ,'语文' AS class, 99 AS score FROM system.dual 
) 
SELECT name AS name , 
max(CASE WHEN class='语文' THEN score ELSE NULL END ) AS '语文', 
max(CASE WHEN class='数学' THEN score ELSE NULL END ) AS '数学' 
FROM student 
GROUP BY name;