CREATE DEFINER=`A_VALID_USER_NAME_HERE`@`localhost` PROCEDURE `usp_produce_survey_report`(IN survey_id VARCHAR(10), IN lang VARCHAR(2))
    SQL SECURITY INVOKER
BEGIN
 
/*---------------------------------------------------------------------------------
I do not guarantee that this will work for you or that it cannot be hacked with
with SQL injections or other malicious intents. 
 
This stored procedure will produce output that you may use to create a report.
It accepts two arguments; The survey id (745) and the language (en).
It parses the column name in the survey table to get the qid.
 
It will copy the answers from the survey table to the survey_report
table if the answer is type S or K. It will get the answers from
the answers table for other types. NOTE: Other types might need to
be added to the if statement.
 
Additionally, the qid and id from the survey table are also copied to
the survey_report table.
 
Then the questions from the questions table, and answers from the answers
and survey_report tables are combined and displayed.  
 
The data in the survey_report table is deleted after the data is displayed.
 
The id from the survey table is displayed as the respondent_id which may
be used to combine the questions and answers from a specific respondent.  
 
You may have to change the prefix on the table names.
Example: survey_answers to my_prefix_answers.
 
Use this to call the procedure.
Syntax:  call survey.usp_produce_survey_report('<SURVERY_ID>', '<LANGUAGE>');
Example: call survey.usp_produce_survey_report('457345', 'en'); 
 
use this to create the table that stores the data
 CREATE TABLE `survey_report` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `qid` int(11) NOT NULL DEFAULT '0',
    `survey_row_id` int(11) NOT NULL DEFAULT '0' COMMENT 'id that is in the survey_<id> table',
    `answer` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
);
*/
 
DECLARE v_col_name VARCHAR (25);
DECLARE v_qid INT;
DECLARE v_col_count INT DEFAULT 0;
DECLARE done INT DEFAULT false;
DECLARE tname VARCHAR(24) DEFAULT CONCAT('survey_survey_',survey_id);
DECLARE counter INT DEFAULT 0;
DECLARE x INT DEFAULT 0;
 
-- select locate ('X','123457X212X1125', 8);  -- use locate to determine location of second X - returns 11
-- select substring('123457X212X1125', 11+1, 7); -- use substring to get the qid - returns 1125
 
DECLARE cur1 cursor for
    SELECT column_name, substring(column_name, 11+1, 7) as qid -- get the qid from the column name.  the 7 might need to be higher depending on the id.  
       FROM information_schema.columns -- this has the column names
       WHERE table_name = tname -- table name created from the id that was passed to the stored procedure
       AND column_name REGEXP 'X'; -- get the columns that have an X
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET done = FALSE;
 
OPEN cur1;
SELECT FOUND_ROWS() INTO v_col_count; -- Get number of column names
   -- SELECT v_col_count; -- print the number of column names
   read_loop: LOOP
      FETCH cur1 INTO v_col_name, v_qid; -- v_col_name is the original column name and v_qid is the qid that is taken from the column name
 
      IF done THEN
         LEAVE read_loop;
      END IF;
      -- SELECT v_col_name, v_qid;   
      SET counter = 1; -- use to compare id's
      SET x = 1; -- used for the while loop
 
      WHILE x < v_col_count DO
         SET @sql := NULL;
         -- SELECT v_col_name, v_qid, counter, x; 
         -- SELECT counter as id, v_col_name, v_qid as qid, x;     
         -- SET @sql = CONCAT ('SELECT id ', ',',v_qid, ' as qid ,', v_col_name,' FROM ', tname, ' WHERE id = ', counter );
         -- I would have to join the survey table below if I did not add the answer (v_col_name). I assume this is faster than another join.
         SET @sql = CONCAT ('INSERT INTO survey_report(qid,survey_row_id,answer) SELECT ',v_qid, ',id,' , v_col_name, '  FROM ', tname, ' WHERE id = ', counter );
 
         -- SELECT @sql;       
         PREPARE stmt FROM @sql;
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;
         -- SELECT counter, x; 
         SET x = x + 1; -- increment counter for while loop
         SET counter = counter + 1; -- increment counter for id's
      END WHILE;
   END LOOP; -- read_loop
   CLOSE cur1;
 
   -- SELECT * FROM survey_report
   -- ORDER BY id, qid;
 
   SET @counter = 0;
 
   SELECT 
      -- @counter:=@counter + 1 AS newindex, -- increment the counter that is in the header
      -- survey_report.id AS id,
      survey_report.survey_row_id as respondent_id, -- the id that copied from the survey table
      -- survey_report.qid,
      question,
      IF(type IN ('S' , 'K'),
         (SELECT answer
            FROM survey_report
            WHERE qid NOT IN (SELECT qid
                    FROM survey_answers)
                    AND id = @counter),
        (SELECT answer
            FROM survey_answers
            WHERE survey_questions.qid = survey_answers.qid
                    AND survey_report.qid = survey_questions.qid
                    AND survey_report.answer = survey_answers.code
                    AND survey_answers.language = lang)) AS answer
            FROM survey_questions
                    JOIN survey_report ON survey_report.qid = survey_questions.qid
                    WHERE survey_questions.sid = survey_id
             AND survey_questions.language = lang
                    ORDER BY survey_report.survey_row_id;
 
   TRUNCATE TABLE survey_report;    
END

CREATE DEFINER=`some_user`@`localhost` PROCEDURE `usp_produce_survey_report`(IN survey_id VARCHAR(10), IN lang VARCHAR(2))
    SQL SECURITY INVOKER
BEGIN
 
/*---------------------------------------------------------------------------------
I do not guarantee that this will work for you or that it cannot be hacked with
with SQL injections or other malicious intents. 
 
This stored procedure will produce output that you may use to create a report.
It accepts two arguments; The survey id (745) and the language (en).
It parses the column name in the survey table to get the qid.
 
It will copy the answers from the survey table to the survey_report
table if the answer is type S or K. It will get the answers from
the answers table for other types. NOTE: Other types might need to
be added to the if statement.
 
Additionally, the qid and id from the survey table are also copied to
the survey_report table.
 
Then the questions from the questions table, and answers from the answers
and survey_report tables are combined and displayed.  
 
The data in the survey_report table is deleted after the data is displayed.
 
The id from the survey table is displayed as the respondent_id which may
be used to combine the questions and answers from a specific respondent.  
 
You may have to change the prefix on the table names.
Example: survey_answers to my_prefix_answers.
 
Use this to call the procedure.
Syntax:  call survey.usp_produce_survey_report('<SURVERY_ID>', '<LANGUAGE>');
Example: call survey.usp_produce_survey_report('457345', 'en'); 
 
use this to create the table that stores the data
 CREATE TABLE `survey_report` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `qid` int(11) NOT NULL DEFAULT '0',
    `survey_row_id` int(11) NOT NULL DEFAULT '0' COMMENT 'id that is in the survey_<id> table',
    `answer` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
);
*/
 
DECLARE v_col_name VARCHAR (25);
DECLARE v_qid INT;
DECLARE v_col_count INT DEFAULT 0;
DECLARE done INT DEFAULT false;
DECLARE tname VARCHAR(24) DEFAULT CONCAT('survey_survey_',survey_id);
DECLARE counter INT DEFAULT 0;
DECLARE x INT DEFAULT 0;
DECLARE r_count INT DEFAULT 0;
 
-- select locate ('X','123457X212X1125', 8);  -- use locate to determine location of second X - returns 11
-- select substring('123457X212X1125', 11+1, 7); -- use substring to get the qid - returns 1125
 
DECLARE cur1 cursor for
    SELECT column_name, substring(column_name, 11+1, 7) as qid -- get the qid from the column name.  the 7 might need to be higher depending on the id.  
       FROM information_schema.columns -- this has the column names
       WHERE table_name = tname -- table name created form the id that was passed to the stored procedure
       AND column_name REGEXP 'X'; -- get the columns that have an X
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET done = FALSE;
 
   OPEN cur1;
   -- SELECT FOUND_ROWS() INTO v_col_count; -- Get number of column names
   -- SELECT v_col_count; -- print the number of column names
   SET r_count = (SELECT COUNT(*) FROM survey_survey_144477);  
   -- SELECT r_count;
 
   read_loop: LOOP
      FETCH cur1 INTO v_col_name, v_qid; -- v_col_name is the original column name and v_qid is the qid that is taken from the column name
 
      IF done THEN
         LEAVE read_loop;
      END IF;
      -- SELECT v_col_name, v_qid;   
      SET counter = 1; -- use to compare id's
      SET x = 1; -- used for the while loop
 
      WHILE x <= r_count DO
         SET @sql := NULL;
         -- SELECT v_col_name, v_qid, counter, x; 
         -- SELECT counter as id, v_col_name, v_qid as qid, x;    
         -- SET @sql = CONCAT ('SELECT id ', ',',v_qid, ' as qid ,', v_col_name,' FROM ', tname, ' WHERE id = ', counter );
         -- I would have to join the survey table below if I did not add the answer (v_col_name). I assume this is faster than another join.
         SET @sql = CONCAT ('INSERT INTO survey_report(qid,survey_row_id,answer) SELECT ',v_qid, ',id,' , v_col_name, '  FROM ', tname, ' WHERE id = ', counter );
 
         -- SELECT @sql;       
         PREPARE stmt FROM @sql;
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;
         -- SELECT counter, x; 
         SET x = x + 1; -- increment counter for while loop
         SET counter = counter + 1; -- increment counter for id's
      END WHILE;
   END LOOP; -- read_loop
   CLOSE cur1;
 
   -- SELECT * FROM survey_report
   -- ORDER BY id, qid;
 
   SET @counter = 0;
 
   SELECT 
      @counter:=@counter + 1 AS newindex, -- increment the counter that is in the header
      survey_report.id,
      survey_report.survey_row_id as respondent_id, -- the id that copied from the survey table
      survey_report.qid,
      question,
      IF(type IN ('S' , 'K'),
         (SELECT answer
            FROM survey_report
            WHERE qid NOT IN (SELECT qid FROM survey_answers)
            AND survey_questions.language = lang
                  AND survey_report.id = @counter),
         (SELECT answer
            FROM survey_answers
            WHERE survey_questions.qid = survey_answers.qid
                  AND survey_report.qid = survey_questions.qid
                  AND survey_report.answer = survey_answers.code
                  AND survey_answers.language = lang
             )
          ) AS answer
       FROM survey_questions
          JOIN survey_report ON survey_report.qid = survey_questions.qid
          WHERE survey_questions.sid = survey_id
          ORDER BY survey_report.survey_row_id, survey_report.id;
 
   TRUNCATE TABLE survey_report;    
END

CREATE DEFINER=`some_user`@`localhost` PROCEDURE `usp_produce_survey_report`(IN survey_id VARCHAR(10), IN lang VARCHAR(2))
    SQL SECURITY INVOKER
BEGIN
 
/*---------------------------------------------------------------------------------
I do not guarantee that this will work for you or that it cannot be hacked with
with SQL injections or other malicious intents. 
 
This stored procedure will produce output that you may use to create a report.
It accepts two arguments; The survey id (745) and the language (en).
It parses the column name in the survey table to get the qid.
 
It will copy the answers from the survey table to the survey_report
table if the answer is type S or K. It will get the answers from
the answers table for other types. NOTE: Other types might need to
be added to the if statement.
 
Additionally, the qid and id from the survey table are also copied to
the survey_report table.
 
Then the questions from the questions table, and answers from the answers
and survey_report tables are combined and displayed.  
 
The data in the survey_report table is deleted after the data is displayed.
 
The id from the survey table is displayed as the respondent_id which may
be used to combine the questions and answers from a specific respondent.  
 
You may have to change the prefix on the table names.
Example: survey_answers to my_prefix_answers.
 
Use this to call the procedure.
Syntax:  call survey.usp_produce_survey_report('<SURVERY_ID>', '<LANGUAGE>');
Example: call survey.usp_produce_survey_report('457345', 'en'); 
 
use this to create the table that stores the data
 CREATE TABLE `survey_report` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `qid` int(11) NOT NULL DEFAULT '0',
    `survey_row_id` int(11) NOT NULL DEFAULT '0' COMMENT 'id that is in the survey_<id> table',
    `answer` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
);
*/
 
DECLARE v_col_name VARCHAR (25);
DECLARE v_qid INT;
DECLARE v_col_count INT DEFAULT 0;
DECLARE done INT DEFAULT false;
DECLARE tname VARCHAR(24) DEFAULT CONCAT('survey_survey_',survey_id);
DECLARE counter INT DEFAULT 0;
DECLARE current_row INT DEFAULT 0;
DECLARE total_rows INT DEFAULT 0;
 
-- select locate ('X','123457X212X1125', 8);  -- use locate to determine location of second X - returns 11
-- select substring('123457X212X1125', 11+1, 7); -- use substring to get the qid - returns 1125
 
DECLARE cur1 cursor for
    SELECT column_name, substring(column_name, 11+1, 7) as qid -- get the qid from the column name.  the 7 might need to be higher depending on the id.  
       FROM information_schema.columns -- this has the column names
       WHERE table_name = tname -- table name created form the id that was passed to the stored procedure
       AND column_name REGEXP 'X'; -- get the columns that have an X
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET done = FALSE;
 
OPEN cur1;
 
   SET total_rows = (SELECT table_rows -- get the number of rows
      FROM INFORMATION_SCHEMA.TABLES
      WHERE table_name = tname);
   -- SELECT total_rows;
 
   read_loop: LOOP
      FETCH cur1 INTO v_col_name, v_qid; -- v_col_name is the original column name and v_qid is the qid that is taken from the column name
 
      IF done THEN
         LEAVE read_loop;
      END IF;
      -- SELECT v_col_name, v_qid;   
      SET counter = 1; -- use to compare id's
      SET current_row = 1; -- used for the while loop
 
      WHILE current_row <= total_rows DO
         SET @sql := NULL;
         -- SELECT v_col_name, v_qid, counter, x; 
         -- SELECT counter as id, v_col_name, v_qid as qid, x;    
         -- SET @sql = CONCAT ('SELECT id ', ',',v_qid, ' as qid ,', v_col_name,' FROM ', tname, ' WHERE id = ', counter );
         -- I would have to join the survey table below if I did not add the answer (v_col_name). I assume this is faster than another join.
         SET @sql = CONCAT ('INSERT INTO survey_report(qid,survey_row_id,answer) SELECT ',v_qid, ',id,' , v_col_name, '  FROM ', tname, ' WHERE id = ', counter );
 
         -- SELECT @sql;       
         PREPARE stmt FROM @sql;
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;
         -- SELECT counter, x; 
         SET current_row = current_row + 1; -- increment counter for while loop
         SET counter = counter + 1; -- increment counter for id's
      END WHILE;
   END LOOP; -- read_loop
   CLOSE cur1;
 
   -- SELECT * FROM survey_report
   -- ORDER BY id, qid;
 
   SET @counter = 0;
 
   SELECT 
      @counter:=@counter + 1 AS newindex, -- increment the counter that is in the header
      survey_report.id,
      survey_report.survey_row_id as respondent_id, -- the id that copied from the survey table
      survey_report.qid,
      question,
      IF(type IN ('S' , 'K'),
         (SELECT answer
            FROM survey_report
            WHERE qid NOT IN (SELECT qid FROM survey_answers)
            AND survey_questions.language = lang
                  AND survey_report.id = @counter),
         (SELECT answer
            FROM survey_answers
            WHERE survey_questions.qid = survey_answers.qid
                  AND survey_report.qid = survey_questions.qid
                  AND survey_report.answer = survey_answers.code
                  AND survey_answers.language = lang
             )
          ) AS answer
       FROM survey_questions
          JOIN survey_report ON survey_report.qid = survey_questions.qid
          WHERE survey_questions.sid = survey_id
          ORDER BY survey_report.survey_row_id, survey_report.id;
 
   TRUNCATE TABLE survey_report;    
END