Question: I have a table that looks like -

```
|--|----|------|------|
|id|name|phone1|phone2|
|--|----|------|------|
|1 |abc |123   |567   |
|2 |xyz |766   |980   |
|--|----|------|------|

```

I need to write a query that can give me output like -

```
|--|----|-----|
|id|name|phone|
|--|----|-----|
|1 |abc |123  |
|1 |abc |567  |
|2 |xyz |766  |
|2 |xyz |980  |
|--|----|-----|

```

Basically I am creating 1 column out of 2 phone number columns and repeating the rows. Can someone please help me with query?

Answer: 
You can use the `array` and `explode` udfs:

```
df.selectExpr("id", "name", "explode(array(phone1, phone2)) as phone").show
+---+----+-----+
| id|name|phone|
+---+----+-----+
|  1| abc|  123|
|  1| abc|  567|
|  2| xyz|  766|
|  2| xyz|  980|
+---+----+-----+

```

where [array(phone1, phone2)](https://spark.apache.org/docs/2.2.0/api/python/pyspark.sql.html#pyspark.sql.functions.array) *creates a new array column* from `phone1` and `phone2`, and [exploding](https://spark.apache.org/docs/2.2.0/api/python/pyspark.sql.html#pyspark.sql.functions.explode) the array column will flatten it and creates a new row for each element in the original array.

---

Or if you have more columns and need to do this programmatically, first collect the columns that need to be stacked or flattened, and then use the *varargs* syntax with the `array` udf to collect them:

```scala
val cols = Seq("phone1", "phone2")
// cols: Seq[String] = List(phone1, phone2)

df.select($"id", $"name", explode(array(cols.head, cols.tail: _*)).as("phone")).show
+---+----+-----+
| id|name|phone|
+---+----+-----+
|  1| abc|  123|
|  1| abc|  567|
|  2| xyz|  766|
|  2| xyz|  980|
+---+----+-----+

```
you can achieve this by simply using `UNION ALL` of same table .

```
SELECT id
    ,name
    ,phone1 AS phone
FROM your_Table

UNION ALL

SELECT id
    ,name
    ,phone2 AS phone
FROM your_Table

```