question Get column values in comma separated value in Support Questions

Get column values in comma separated value

ChineduLB — Wed, 15 Apr 2020 18:30:24 GMT

I have a source table Like

ID	USER	DEPT
1	User1	Admin
2	User1	Accounts
3	User2	Finance
4	User3	Sales
5	User3	Finance

I want to generate a DataFrame like this

ID	USER	DEPARTMENT
1	User1	Admin,Accounts
2	User2	Finance
3	User3	Sales,Finance

Re: Get column values in comma separated value

pauldefusco — Wed, 15 Apr 2020 19:08:14 GMT

Hi Chinedu,

This should help: https://stackoverflow.com/questions/48406304/groupby-and-concat-array-columns-pyspark

Thanks,

Paul

Re: Get column values in comma separated value

ChineduLB — Wed, 15 Apr 2020 19:27:18 GMT

Thanks @pauldefusco

I would like to do it in spark - scala

Re: Get column values in comma separated value

Shu_ashu — Thu, 16 Apr 2020 00:06:12 GMT

Hi @ChineduLB ,

You can use `.groupBy` and `concat_ws(",",collect_list)` functions and to generate `ID` use `row_number` window function.

val df=Seq(("1","User1","Admin"),("2","User1","Accounts"),("3","User2","Finance"),("4","User3","Sales"),("5","User3","Finance")).toDF("ID","USER","DEPT")

import org.apache.spark.sql.expressions.Window

df.groupBy("USER"). agg(concat_ws(",",collect_list("DEPT")).alias("DEPARTMENT")). withColumn("ID",row_number().over(w)). select("ID","USER","DEPARTMENT").show()