Let's assume your main table is called df:
+---------+-----------+-----------+------+------+------+
|survey_id|response_id|person_name|Q1D102|Q1D103|Q1D105|
+---------+-----------+-----------+------+------+------+
|xyz |xyz |john |1 |2 |1 |
|abc |abc |foo |3 |1 |1 |
|def |def |bar |2 |2 |2 |
+---------+-----------+-----------+------+------+------+
and the mapping table is called df2:
+-----------+-------------+-------------------+---------+-----------+
|question_id|question_name|question_text |choice_id|choice_desc|
+-----------+-------------+-------------------+---------+-----------+
|Q1D102 |Gender |What is your gender|1 |Male |
|Q1D102 |Gender |What is your gender|2 |Female |
|Q1D102 |Gender |What is your gender|3 |Diverse |
|Q1D103 |Age |What is your age |1 |20 - 50 |
|Q1D103 |Age |What is your age |2 |50 > |
|Q1D105 |work_status |Do you work |1 |Yes |
|Q1D105 |work_status |Do you work |2 |No |
+-----------+-------------+-------------------+---------+-----------+
We can construct a dynamic unpivot expression as below:
val columns = df.columns.filter(c => c.startsWith("Q1D"))
val data = columns.map(c => s"'$c', $c").mkString(",")
val finalExpr = s"stack(${columns.length}, $data) as (question_id, choice_id)"
With 3 questions, we get the following expression (Q1D102, Q1D103 and Q1D105): stack(3, 'Q1D102', Q1D102,'Q1D103', Q1D103,'Q1D105', Q1D105) as (question_id, choice_id)
Finally, we use the constructed variable:
df = df
.selectExpr("survey_id", "response_id", "person_name", finalExpr)
.join(df2, Seq("question_id", "choice_id"), "left")
You get this result:
+-----------+---------+---------+-----------+-----------+-------------+-------------------+-----------+
|question_id|choice_id|survey_id|response_id|person_name|question_name|question_text |choice_desc|
+-----------+---------+---------+-----------+-----------+-------------+-------------------+-----------+
|Q1D102 |1 |xyz |xyz |john |Gender |What is your gender|Male |
|Q1D102 |2 |def |def |bar |Gender |What is your gender|Female |
|Q1D102 |3 |abc |abc |foo |Gender |What is your gender|Diverse |
|Q1D103 |1 |abc |abc |foo |Age |What is your age |20 - 50 |
|Q1D103 |2 |xyz |xyz |john |Age |What is your age |50 > |
|Q1D103 |2 |def |def |bar |Age |What is your age |50 > |
|Q1D105 |1 |xyz |xyz |john |work_status |Do you work |Yes |
|Q1D105 |1 |abc |abc |foo |work_status |Do you work |Yes |
|Q1D105 |2 |def |def |bar |work_status |Do you work |No |
+-----------+---------+---------+-----------+-----------+-------------+-------------------+-----------+
Which I think is what you need (just unordered), good luck!
