ashK ashK - 1 month ago 21
Scala Question

Spark sql to do a excel transpose paste

I have a couple of scenarios to convert multiple rows into comma separated single column and in another scenario I have a comma separated column which I have to insert as multiple rows. I use spark scala.

Scenario 1



Select names from employee where role = 'Engineer'



  1. Mark

  2. Steve

  3. Krishna



I need to convert it as Mark, Steve, Krishna.

Scenario 2



I have the same Mark, Steve, Krishna which I need to insert as 3 rows.

Any help?

Answer

For Scenario 2, you can try something like this

select name as value from table union select ename from table 

You will have to make it dynamic though.