mysql - 行到列的转换

Question

多次加入后，我得到了原始结果。

+----------------------------------------------------------------------+
| Results                                                              |
+----+----------+-------------+----------+-----------+-----------------+
| id | group_id | question_id | question | answer_id | answer | input  |
+----+----------+-------------+----------+-----------+-----------------+
| 1  | 10001    | 1           | How old  | 1         | 25     | NULL   |  
| 2  | 10001    | 2           | What like| 3         | Cola   | NULL   |
| 3  | 10001    | 2           | What like| 4         | Other  | HotDog |
| 4  | 10001    | 3           | City     | 5         | NYC    | NULL   |
| 5  | 10001    | 4           | Name     | 7         | Other  | Alex   |
| 6  | 10002    | 1           | How old  | 1         | 25     | NULL   | 
| 7  | 10002    | 2           | What like| 6         | Candy  | NULL   |
| 8  | 10002    | 3           | City     | 8         | LA     | NULL   | 
| 9  | 10002    | 4           | Name     | 7         | Other  | Roman  | 
+----+----------+-------------+----------+-----------+--------+--------+

但现在我想通过 group_id 在“单行 View ”中查看它。如:

+----+----------+-------------+----------+-----------+
| id | How Old  |  What like  | City     |  Name     |
+----+----------+-------------+----------+-----------+
| 1  | 25       | Cola,HotDog | NYC      | Alex      |
| 2  | 25       | Candy       | LA       | Roman     |  
+----+----------+-------------+----------+-----------+

我不知道正常的 group_by/concat 构造。我必须做什么？

Answer 1

SET @i := 1;
SELECT @i := @i + 1 AS `id`
, GROUP_CONCAT(CASE WHEN question = 'How old' THEN answer ELSE NULL END) AS `How Old`
, GROUP_CONCAT(CASE WHEN question = 'What like' THEN IF(answer='Other', input, answer) ELSE NULL END) AS `What like`
, ....
FROM theTable
GROUP BY group_id
;

group_concat 忽略 null 值，仅当收到的唯一值为 null 时才返回 null。对于 CASE WHEN THEN ELSE END 语句，您可以轻松使用 IF(,,) ，就像在“其他”检查中使用的那样；但 CASE 更可移植(MS SQL Server 最近才添加了对此类 IF 的支持。)