Create a Vertical Table in Spark 2

Question

How to create a vertical table in Spark 2 SQL.

I am building a ETL using Spark 2 / SQL / Scala. I have data in normal table structure like.

Input Table:

| ID | A  | B  | C  | D  |
| 1  | A1 | B1 | C1 | D1 |
| 2  | A2 | B2 | C2 | D2 |

Output Table:

| ID | Key | Val |
| 1  | A   | A1  |
| 1  | B   | B1  |
| 1  | C   | C1  |
| 1  | D   | D1  |
| 2  | A   | A2  |
| 2  | B   | B2  |
| 2  | C   | C2  |
| 2  | D   | D2  |

Answer 1

This could do the trick as well:

Input Data:

+---+---+---+---+---+
|ID |A  |B  |C  |D  |
+---+---+---+---+---+
|1  |A1 |B1 |C1 |D1 |
|2  |A2 |B2 |C2 |D2 |
|3  |A3 |B3 |C3 |D3 |
+---+---+---+---+---+

Zip the column header and no of columns to be included:

val cols = Seq("A","B","C","D") zip Range(0,4,1)
df.flatMap(r => cols.map(i => (r.getString(0),i._1,r.getString(i._2 + 1)))).toDF("ID","KEY","VALUE").show()

Result should look like this:

+---+---+-----+
| ID|KEY|VALUE|
+---+---+-----+
|  1|  A|   A1|
|  1|  B|   B1|
|  1|  C|   C1|
|  1|  D|   D1|
|  2|  A|   A2|
|  2|  B|   B2|
|  2|  C|   C2|
|  2|  D|   D2|
|  3|  A|   A3|
|  3|  B|   B3|
|  3|  C|   C3|
|  3|  D|   D3|
+---+---+-----+

Good Luck!!