Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: Retrieve surveys survey results in normalized form

Retrieve surveys survey results in normalized form 2 years 2 weeks ago #104035

  • Mazi
  • Mazi's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 5841
  • Thank you received: 347
  • Karma: 261
Since there are no more changes at the Limesurvey 2.0 core, you should not have any problems later with that version.

Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 1 year 11 months ago #104997

  • quaint
  • quaint's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 14
  • Thank you received: 1
  • Karma: 0
Hello Lordzoster, Mazi,

Getting an export of normalised survey data in the format specified by itsme (eg)
id, attribute_1, attribute_2, question, answer
1, "Employer 1", "Site 1", "What do you think about X?", "It's great"
1, "Employer 1", "Site 1", "What do you think about Y?", "It's rubbish"
1, "Employer 1", "Site 1", "What do you think about Z?", "It's OK"
2, "Employer 1", "Site 2", "What do you think about X?", "It's OK"
2, "Employer 1", "Site 2", "What do you think about Y?", "It's great!"
2, "Employer 1", "Site 2", "What do you think about Z?", "It's rubbish!"

would be very, very useful for my organisation.

I haven't yet updated to 2.05; the next clear window of opportunity to do so (and rebuild the server!) will be towards the late end of March, but a plugin would definitely be installed and regularly used!
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 1 year 11 months ago #105000

  • lordzoster
  • lordzoster's Avatar
  • Offline
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
Hi all
I'm not disappeared :)
Yesterday I finally managed to compact all the logic within a single SQL (I'm a db guy after all) getting rid of the PHP part except for the JSON extraction (too sad MySQL doesn't offer an out-of-the-box solution for returning JSON directly yet).
Moreover I wrote a little bit cleaner and reusable code, without the hardcoded character positions (the "X" of the SGQA format in the column names).

Here it is an early release of the SQL:
DROP PROCEDURE IF EXISTS spLime_NormalizeIt;
 
DELIMITER //
 
CREATE PROCEDURE spLime_NormalizeIt (survey_code INT)
BEGIN
/*
	Author: Raffaele Turra - SyntegraDMC
	Created: 2014-02-08
 
	Create an UNPIVOT from survey results
	considering only the columns containing the questions
	Note: LS creates acolumn for each question, in the sidXgidXqid format
	and values are answers' codes
*/
 
SET group_concat_max_len=15000;
SET @r_query = NULL;
# SET survey_code = 869765;
SET @drop_stmt = CONCAT("DROP TABLE IF EXISTS lime_survey_", survey_code, "_normalized;");
SET @create_stmt = CONCAT("CREATE TABLE lime_survey_", survey_code, "_normalized LIKE lime_survey__normalized;");
 
SELECT GROUP_CONCAT(DISTINCT
	CONCAT(
		"SELECT	LS.token
		, LS.datestamp
		, \"", RIGHT(g.group_name, 5), "\" AS codice_reato
		, \"", q.Title, "\"	AS codice_domanda
		, LA.qid
		, LS.`", c.column_name, "`	AS answer_code
		, LA.assessment_value
	from lime_answers AS LA
	INNER JOIN `lime_survey_", survey_code , "` AS LS
		ON LA.code = LS.`", c.column_name, "`
		AND LA.qid = substring_index(\"", c.column_name, "\", 'X', -1)"
		) SEPARATOR " UNION ALL "
) INTO @r_query
from information_schema.columns c
left join lime_groups AS g
	ON g.gid = substring_index(substring_index(column_name, 'X',2) , 'X', -1)
left join lime_questions AS q
	ON g.gid = q.gid
	and q.qid = substring_index(column_name, 'X', -1)
where substring_index(column_name, 'X', 1) = substring_index(table_name, '_', -1)
	AND substring_index(column_name, 'X', 1) = CAST(survey_code AS char(10))
ORDER BY q.question_order;
 
SET @r_query = CONCAT("INSERT INTO lime_survey_"
	, survey_code
	, "_normalized 
	"
	, @r_query);
 
PREPARE dropstmt FROM @drop_stmt;
EXECUTE dropstmt;
DEALLOCATE PREPARE dropstmt;
 
PREPARE createstmt FROM @create_stmt;
EXECUTE createstmt;
DEALLOCATE PREPARE createstmt;
 
PREPARE selstmt FROM @r_query;
EXECUTE selstmt;
DEALLOCATE PREPARE selstmt;
END //
 
DELIMITER ;
 
CALL spLime_NormalizeIt (869765);
This code basically creates a stored procedure which accepts the survey sid as an input, and creates a "normalized" table using an existing one as a template, here the CREATE statement:
DROP TABLE IF EXISTS lime_survey__normalized;
 
CREATE TABLE lime_survey__normalized (
	token varchar(50)
	, `datestamp` datetime NOT NULL
	, codice_reato varchar(50)
	, codice_domanda varchar(50)
	, qid INT
	, answer_code	varchar(5)
	, assessment_value int
);

Note: "codice_domanda" is italian for "question_code" and it refers to the "question title" as named in LS;
"codice_reato" is specific for my LS application, and corresponds to the code entered for the question group.
HTH
Last Edit: 1 year 11 months ago by lordzoster.
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 1 year 11 months ago #105056

  • lordzoster
  • lordzoster's Avatar
  • Offline
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
I update the SELECT, there could be an issue if the survey includes free text answers.
SELECT GROUP_CONCAT(DISTINCT
	CONCAT(
		"SELECT	LS.token
		, LS.submitdate
		, \"", RIGHT(g.group_name, 5), "\" AS codice_reato
		, \"", q.Title, "\"	AS codice_domanda
		, LA.qid
		, LS.`", c.column_name, "`	AS answer_code
		, LA.assessment_value
	FROM `lime_survey_", survey_code , "` AS LS
	LEFT JOIN lime_answers AS LA
		ON LA.code = LS.`", c.column_name, "`
		AND LA.qid = substring_index(\"", c.column_name, "\", 'X', -1)"
		) SEPARATOR " UNION ALL "
) INTO @r_query
from information_schema.columns c
left join lime_groups AS g
	ON g.gid = substring_index(substring_index(column_name, 'X',2) , 'X', -1)
left join lime_questions AS q
	ON g.gid = q.gid
	and q.qid = substring_index(column_name, 'X', -1)
where substring_index(column_name, 'X', 1) = substring_index(table_name, '_', -1)
	AND substring_index(column_name, 'X', 1) = CAST(survey_code AS char(10))
ORDER BY q.question_order;
The administrator has disabled public write access.
  • Page:
  • 1
  • 2
Moderators: ITEd
Time to create page: 0.477 seconds