I'm using survey monkey's API to dump survey data into a SQL data warehouse.
So I'm dumping response data into a table 'resonses' thusly:
survey_id, Q1, Q2, Q3, Q4, Q5 survey3944, 848948344, 430984384, 43094839, 238094983, 2380284
Then I have a dimensional table 'answer_key' that has the id/ text combos. Such as
ID, text 848948344, 5 - very satisfied
I'm wondering how I can join the 5 answer columns above into meaningful text (or at least the integer) without doing 5 joins.
I mean say I have the table 'responses' and the table 'answer_key'.
My initial thought would be go through the motion:
Select r.survey_id, a.text, a2.text, a3.text, a4.text, a5.text from responses r inner join answer_key a on r.q1 = a.id inner join answer_key a2 on r.q2 = a2.id inner join answer_key a3 on r.q3 = a3.id inner join answer_key a4 on r.q4 = a4.id inner join answer_key a5 on r.q5 = a5.id
Is that the best way to accomplish this? Just wondering. Especially if the survey is expanded to 10 questions, or what not.
I realize I can have a row for each question response rather than each survey for the table structure --- that's a possibility but I think it's a bit more difficult given the structure of the JSON, but maybe it's more logical.