xCEZAREx xCEZAREx - 18 days ago 12
MySQL Question

Row to column transform

After multiple join, I have raw results.

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


But now I want to see it in "one row view" by group_id.
Such as:

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


I don`t know normal group_by/concat construction for that. What must I do?

Answer
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 ignores null values, only returning null if the only values it received were null. For the CASE WHEN THEN ELSE END statements you could easily use IF(,,) like was used in the "other" check; but CASE is more portable (MS SQL Server only relatively recently added support for those kinds of IFs.)