Execute Spark sql query within withColumn clause is Spark Scala

Question

I have a dataframe which has one of the column called "Query" having the select statement present. Want to execute this query and create a new column having actual results from the query within the same dataframe.

+--------------+-----------+-----+--------------------------------+
|DIFFCOLUMNNAME|DATATYPE   |ISSUE|QUERY                           |
+--------------+-----------+-----+--------------------------------+
|Firstname     |StringType |YES  |Select Firstname from TempView  limit 1|
|LastName      |StringType |NO   |Select LastName from TempView  limit 1  |
|Designation   |StringType |YES  |Select Designation from TempView limit 1|
|Salary        |IntegerType|YES  |Select Salary from TempView    limit 1 |
+--------------+-----------+-----+--------------------------------+

When I try below

DF.withColumn("QueryResult", SparkConfiguration.sparkSession.sql(col("QUERY"))) 

Getting error as Type mismatch, Required String found column. 

I am thinking I need to use UDF here. But not sure how to write and use. Please helpspark