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.

  • 1
    Perhaps normalize the responses table? Repeating group is not handy in your case. – Marco Jul 12 '16 at 14:26
  • That's an idea -- you mean put a row for each question, instead of for each survey? – user45867 Jul 12 '16 at 14:27
  • 1
    That is right. Then you are also not limited to a number of questions per survey. – Marco Jul 12 '16 at 14:29

Normalise the table responses so there is 1 question per row. Then you are also not limited to a number of questions per survey.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.