Spark Sql合并多行内容为一行

用到的函数及定义

concat_ws(sep, [str | array(str)]+) - Returns the concatenation of the strings separated by sep.
Examples:

SELECT concat_ws(’ ', ‘Spark’, ‘SQL’);
Spark SQL

collect_set(expr) - Collects and returns a set of unique elements.

数据用例

我们要把第四列的字符串合并为一行

select 
	Asset_a
	, concat_ws(',',collect_set(NT_Login)) as ntlogin 
from (
	select 
	Asset
	,Platform
	,UserOrBatch
	,NT_Login
	from eip_rewards_usage  
	where Platform='hercules' 
	and UserOrBatch='User'
	)
group by 1
order by 1 asc

先对我们所需的数据增加限制进行group by，然后组内NT_Login数据项collect_set在一起，concat_ws进行合并，’,'作为分隔符

sparksql函数文档：
https://docs.databricks.com/spark/latest/spark-sql/language-manual/functions.html#concat_ws