Mysql/Mariadb查询在查询数据时动态地将行转换为列

Question

我查看了 Mysql query to dynamically convert rows to columns 上的答案但我无法让它工作，因为我需要连接表来获取所有信息。您能否帮我将“问题”作为列名称，如此处所示？

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

数据如下:

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

我正在使用此代码:

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

其中提供:

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...

当所有数据都在一个表中并且我不必使用联接时，此代码有效。

    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; 

这是一次没有 REPLACE 函数的尝试(我想使用它)。

 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

这有效，但我不想创建另一个表。

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

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