title: Mysql / Mariadb query to dynamically convert rows to columns while querying the data
body: <p>I looked at the answers at <a href="https://stackoverflow.com/questions/14834290/mysql-query-to-dynamically-convert-rows-to-columns">Mysql query to dynamically convert rows to columns</a> 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?</p>
  
  <pre><code>datestamp           pay        benefits ...    career advancement
  ---------------------------------------------------------------------   
  2/16/2017 11:55     Somewhat   Slightyly       Slightly
  2/16/2017 11:55     Agree      Somewhat        Very
  </code></pre>
  
  <p>The data looks like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I'm using this code:</p>
  
  <pre><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'
        ;  
  </code></pre>
  
  <p>Which provides:</p>
  
  <pre><code>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...
  </code></pre>
  
  <p>This code worked when all of the data was in one table and I did not have to use joins.</p>
  
  <pre><code>    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; 
  </code></pre>
  
  <p>This was an attempt without the REPLACE function (which I would like to use).</p>
  
  <pre><code> 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;
  </code></pre>