连接表并转置列和行 [英] join tables and transpose columns and rows

问题描述

我有一个看起来像这样的表，名为Survey_1:

I have one table that looks like this called survey_1:

================================================
|id  | token  |     1X2X1     |    1X2X2       |
=====+========+===============+================|
| 1  |   1    |    YES        | Justin Beiber  |
|----+--------+---------------+----------------|
| 2  |   1    |    YES        | Britney Spears |
|----+--------+---------------+----------------|

注释:1X2X1代表-调查ID X组ID X问题ID

note: 1X2X1 represents- survey-id X group-id X question-id

我还有另一个表，名为Survey_questions:

I have another table called survey_questions:

===============================================================
|sid |   gid  |   qid  |             question                 |
=====+========+===============+===============================|
| 1  |  2     |    1   |  Do you listen to music?             |
|----+--------+-----------------------------------------------|
| 1  |  2     |    2   |  Who is your favorite music artists? |
|----+--------+-----------------------------------------------|

sid(调查ID)，gid(group-id)和qid(question-id)定义了该表中的特定问题

The sid (survey-id), gid (group-id) and qid(question-id) define that specific question in this table

我需要一个查询，它会给我这样的结果:

I need a query that will give me a result like this:

======================================================
|           Question                  |   Answer     |
=========+===========+===============================|
| Do you listen to music?             |    YES       |            
|----------------------------------------------------|
| Who is your favorite music artists? | Justin Beiber| 
|----------------------------------------------------|

注意:我的数据库包含成千上万的此类列，因此以这种格式编辑每个调查以使其完全匹配将非常耗时.

NOTE: My database contains thousands of these columns, so it would be very time consuming to edit every survey to match up perfectly in this format.

有人可以帮忙吗?谢谢

推荐答案

您需要使用'UNPIVOT'，不幸的是MySQL不支持.您可以通过如下方式对列名进行硬编码(但您需要提前知道所有列)来做类似的事情:

You need to use 'UNPIVOT', which MySQL unfortunately does not support. You can do a similar thing by hardcoding the column names (but you need to know all the columns in advance) like this:

SELECT survey_questions.Question,
       CASE survey_questions.qid 
         WHEN 1 THEN survey_1.`1X2X1`
         WHEN 2 THEN survey_1.`1X2X2`
         WHEN 3 THEN survey_1.`1X2X3`
         WHEN 4 THEN survey_1.`1X2X4`
       // ...
       END as Answer
FROM survey_questions
JOIN survey_1 
  ON survey_questions.qid = survey_1.id
  AND survey_questions.gid = survey_1.token_id
WHERE survey_questions.sid = 1

当然，您始终可以使用某些脚本语言为您生成列名.例如，您可以使用以下存储过程:

Of course, you can always use some scripting language to generate the column names for you... For example, here is a stored procedure you can make:

CREATE PROCEDURE 'get_qa_for_survey'
(
  IN surveyId INT
)
BEGIN
  DECLARE query1 TEXT; 
  SET @tableName = 'survey_' + surveyId;
  SET query1 = 'SELECT survey_questions.Question,
       CASE survey_questions.qid ';

  DECLARE col_names CURSOR FOR
    SELECT column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = @tableName
    AND (column_name LIKE surveyId +'X%');
    ORDER BY ordinal_position;

  select FOUND_ROWS() into num_rows;

  SET i = 1;
  the_loop: LOOP    
     IF i > num_rows THEN
        CLOSE col_names;
        LEAVE the_loop;
     END IF;

     FETCH col_names 
     INTO col_name;     
     SET query1 = query1 + ' WHEN ' + i + ' THEN ' + @tableName + '.' + col_name
     SET i = i + 1;  
  END LOOP the_loop;

  SET query1 = query1 + ' END as Answer
    FROM survey_questions
    JOIN ' + @tableName + '
      ON survey_questions.qid = ' + @tableName + '.id
      AND survey_questions.gid = ' + @tableName + '.token_id
    WHERE survey_questions.sid = ' + surveyId; 


  SET @Sql = query1;        
  PREPARE STMT FROM @Sql; 
  EXECUTE STMT; 
  DEALLOCATE PREPARE STMT;
END

这篇关于连接表并转置列和行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！