Normalization of survey results

More
3 years 8 months ago - 3 years 8 months ago #112936 by dante_deo
dante_deo created the topic: Normalization of survey results
Hi there,

I am currently on version 2.00b130611 and I wanna make the survey results normalized (pivot/unpivot) such that I can query results easier from DB. There exists a similar topic (ie. Retrieve surveys survey results in normalized form ) but the given code does not work since the naming of columns in survey answer tables (ie. lime_survey_<survey_code>) does not match the following format:
<survey_id(sid)>X<question_group_id(gid)>X<question_id(qid)><answer_code>

As far as I have discovered there are column names of the following formats:
<survey_id(sid)>X<question_group_id(gid)>X<question_id(qid)>
<survey_id(sid)>X<question_group_id(gid)>X<question_id(qid)>other
<survey_id(sid)>X<question_group_id(gid)>X<question_id(qid)>comment
<survey_id(sid)>X<question_group_id(gid)>X<parent_question_id(parent_qid)><question_title>
<survey_id(sid)>X<question_group_id(gid)>X<parent_question_id(parent_qid)><question_title>#<scale_id>
...

The problem I am faced with is that "I cannot find the pattern (logic) used in column naming when the table for survey answers is being created". If I can I will be able to reverse engineer the normalized form, but I cannot.

I need help in either:
1-The sql that normalizes the answers data structure in a format similar to the following (aligned with the id,token,submitdate,lastpage,startlanguage,startdate,datestamp,ipaddr,refurl columns) :
survey_id | question_group_id | question_parent_id | question_id | scale_id | answer_code
2-The logic behind naming columns.
Last Edit: 3 years 8 months ago by dante_deo. Reason: syntax correction

Please Log in or Create an account to join the conversation.

More
3 years 8 months ago #112953 by tpartner
tpartner replied the topic: Normalization of survey results
The columns are named with the SGQA identifier appended by:
- the sub-question code if any
- "other" if that option for the question has been activated
- "comment" if it is a "with comment" question type


.

Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

Please Log in or Create an account to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now
Join our Newsletter!