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.
Select names from employee where role = 'Engineer'
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.