Mysql / Mariadb query to dynamically convert rows to columns while querying the data

Question

I looked at the answers at Mysql query to dynamically convert rows to columns but I could not get it to work because I need to join tables to get all of the information. Can you please help me place the 'question' as the column name as shown here?

datestamp           pay        benefits ...    career advancement
---------------------------------------------------------------------   
2/16/2017 11:55     Somewhat   Slightyly       Slightly
2/16/2017 11:55     Agree      Somewhat        Very

The data looks like:

id  datestamp        survey_col           value
-----------------------------------------------
1   2/16/2017 11:55 885457X234X1368SQ001    A3
1   2/16/2017 11:55 885457X234X1368SQ002    A4
1   2/16/2017 11:55 885457X234X1368SQ003    A4
1   2/16/2017 11:55 885457X234X1368SQ004    A3
1   2/16/2017 11:55 885457X234X1368SQ005    A2
1   2/16/2017 11:55 885457X234X1368SQ006    A3
1   2/16/2017 11:55 885457X234X1368SQ007    A4
1   2/16/2017 11:55 885457X234X1368SQ008    A3
1   2/16/2017 11:55 885457X234X1368SQ009    A4
1   2/16/2017 11:55 885457X234X1368SQ010    A1

I'm using this code:

   SELECT T.id, T.datestamp, SQ.question, 
      IF(type IN ("K", "N", "S", "T", "Y", "*") AND type NOT IN ("F"),
         T.value,
         IF(parent_qid = 0, SA.answer, SA2.answer)
      ) as answer
      FROM survey_questions SQ 
      JOIN survey_lookup SL ON SL.qid = SQ.qid
      JOIN tmp T ON T.survey_col = SL.survey_col
      LEFT JOIN survey_answers SA ON SA.qid = SQ.qid
         AND SA.code = T.value
         AND SA.language = 'en'
      LEFT JOIN survey_answers SA2 on SA2.qid = SQ.parent_qid
         AND SA2.code = T.value
         AND SA2.language = 'en'
      WHERE SQ.language = 'en'
      ;  

Which provides:

id  datestamp       question                            answer
----------------------------------------------------------------
1   2/16/2017 11:55 Pay                                 Somewhat
1   2/16/2017 11:55 Benefits (health plan, leave, etc.) Slightly
1   2/16/2017 11:55 Career Advancement                  Slightly
1   2/16/2017 11:55 Access to Training                  Somewhat
1   2/16/2017 11:55 Leadership Style                    Very
1   2/16/2017 11:55 Manager/Supervisor Style            Somewhat
1   2/16/2017 11:55 Please enter comments               The company's policies...

This code worked when all of the data was in one table and I did not have to use joins.

    SET @sql = NULL;
   SET SESSION GROUP_CONCAT_MAX_LEN = 1000000;  -- default is 1024
   SELECT
      GROUP_CONCAT(DISTINCT
         CONCAT(
            'MAX(IF(question = ''', REPLACE(question,"'", "\\'"), ''', answer, NULL)) AS ''', REPLACE(question,"'", "\\'"), ''''
         )
      ) INTO @sql
      FROM tmp; 

      SET @sql = CONCAT('SELECT row_id, submitdate, ', @sql, ' FROM ', survey_report, ' GROUP BY row_id');

      IF DEBUG = 1 THEN
         SELECT @sql;   
      END IF;

      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt; 

This was an attempt without the REPLACE function (which I would like to use).

 SELECT 
  GROUP_CONCAT(DISTINCT
  CONCAT(
  'MAX(IF(SQ.question = ', '''SQ.question''',' 
      IF(type IN ("K", "N", "S", "T", "Y", "*") AND type NOT IN ("F"),
        T.value,
        IF(parent_qid = 0, SA.answer, SA2.answer)
      ) as answer, NULL)) as answer', '''SQ.question''')
  ) INTO @sql
  FROM survey_questions SQ 
  JOIN survey_lookup SL ON SL.qid = SQ.qid
  JOIN tmp T ON T.survey_col = SL.survey_col
  LEFT JOIN survey_answers SA ON SA.qid = SQ.qid
     AND SA.code = T.value
     AND SA.language = 'en'
  LEFT JOIN survey_answers SA2 on SA2.qid = SQ.parent_qid
     AND SA2.code = T.value
     AND SA2.language = 'en'
  WHERE SQ.language = 'en';  

  SET @sql = CONCAT('SELECT id, datestamp, ', @sql, ' FROM tmp GROUP BY id');

  SELECT @sql;   

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

Answer 1

This worked but I did not want to create another table.

DROP TEMPORARY TABLE  IF EXISTS `tmp2`;
CREATE TEMPORARY TABLE tmp2
   SELECT T.id, T.datestamp, SQ.question, 
      IF(type IN ("K", "N", "S", "T", "Y", "*") AND type NOT IN ("F"),
         T.value,
         IF(parent_qid = 0, SA.answer, SA2.answer)
      ) as answer
      FROM survey_questions SQ 
      JOIN survey_lookup SL ON SL.qid = SQ.qid
      JOIN tmp T ON T.survey_col = SL.survey_col
      LEFT JOIN survey_answers SA ON SA.qid = SQ.qid
         AND SA.code = T.value
         AND SA.language = 'en'
      LEFT JOIN survey_answers SA2 on SA2.qid = SQ.parent_qid
         AND SA2.code = T.value
         AND SA2.language = 'en'
      WHERE SQ.language = 'en'
      ;  

-- select * from tmp2;

         SET @sql = NULL;
   SET SESSION GROUP_CONCAT_MAX_LEN = 1000000;  -- default is 1024
   SELECT
      GROUP_CONCAT(DISTINCT
         CONCAT(
            'MAX(IF(question = ''', REPLACE(question,"'", "\\'"), ''', answer, NULL)) AS ''', REPLACE(question,"'", "\\'"), ''''
         )
      ) INTO @sql
      FROM tmp2;
      SET @sql = CONCAT('SELECT id, datestamp, ', @sql, ' FROM tmp2 GROUP BY id');

      SELECT @sql;   

      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;