Hive SQL常考面试题

多列转多行

test:
result:

select
	a.name,
	b.subject,
	b.score
from test as a
lateral view explode(
	map(
		'语文',chinese,
		'数学',math,
		'英语',english
	)
) b as subject, score;

select
	a.name,
	b.subject,
	b.score
from test as a
lateral view explode(
	str_to_map(concat(
		'语文=',chinese,
		'&数学=',math,
		'&英语=',english
	), '&', '=')
) b as subject, score;

多行转多列

test:

result: