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.