how to pivot /transpose rows of a column in to individual columns in spark-scala without using the pivot method

Please check below image for the reference to my use case

enter image description here

1 answer

  • answered 2020-11-20 13:04 Srinivas

    Use groupBy,pivot & agg functions. Check below code. Added inline comments.

    scala> df.show(false)
    +----------+------+----+
    |tdate     |ttype |tamt|
    +----------+------+----+
    |2020-10-15|draft |5000|
    |2020-10-18|cheque|7000|
    +----------+------+----+
    
    scala> df
    .groupBy($"tdate") // Grouping data based on tdate column.
    .pivot("ttype",Seq("cheque","draft")) // pivot based on ttype and "draft","cheque" are new column name
    .agg(first("tamt")) // aggregation by "tamt" column.
    .show(false)
    
    +----------+------+-----+
    |tdate     |cheque|draft|
    +----------+------+-----+
    |2020-10-18|7000  |null |
    |2020-10-15|null  |5000 |
    +----------+------+-----+