Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Normalization of survey results

  • dante_deo
  • dante_deo's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
9 years 5 months ago - 9 years 5 months ago #112936 by dante_deo
Normalization of survey results was created by dante_deo
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:
Code:
<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:
Code:
<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) :
Code:
survey_id | question_group_id | question_parent_id | question_id | scale_id | answer_code
2-The logic behind naming columns.
Last edit: 9 years 5 months ago by dante_deo. Reason: syntax correction
The topic has been locked.
  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
9 years 5 months ago #112953 by tpartner
Replied by tpartner on 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.
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose