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!"

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);

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
);

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;