你可以写：

dataFrame.registerTempTable("temp1")
val result = sqlContext.sql (here put same SQL as in question)

或在spark 2.0中：

dataset.createTempView("temp1")
val result = sparkSession.sql(here put same SQL as in question)

或者，可以使用数据集api：

val mobileUDF = udf {
    (name : String, mobile : String) => if (name == "Mrs.") mobile else null;
}
val phoneUDF = udf {
    (name : String, phone: String) => if (name == "Mrs.") phone else null;
}

dataset.withColumn("newMobile", mobileUDF($"name", $"mobile"))
    .withColumn("newPhone", phoneUDF($"name", $"phone"))
    .groupBy($"id")
    .agg(max(col("newMobile")), max(col("newPhone")))

尝试：

df.groupBy('id).agg(
  max(when('name === "Mrs.", 'mobile)).alias("mobile"),
  max(when('name === "Dr.", 'phone)).alias("phone")
)