How to concatenate records based on a GROUP BY?

Question

I have a table with a structure like:

| id | textfield | text_group | 
| 1  | yes       | 123        | 
| 2  | y         | 123        | 
| 3  | no        | abc        | 
| 4  | n         | abc        |

I'd like to return all records, but such that I get:

| colA  | text_group | 
| yes y | 123        |
| no n  | abc        |

Is this possible simply with a query, or will I need to use some programming to format it as such? I would have done a group by, but then I lose the information in result ColA

score 4 · Accepted Answer · answered Sep 23 '14 at 17:12

4

The group_concat aggregate function should do the trick:

SELECT   group_concat (textfield SEPARATOR ' ') AS colA, text_group
FROM     my_table
GROUP BY text_group

answered Sep 23 '14 at 17:12

Mureinik

277,661
50
283
320

score 2 · Answer 2 · answered Sep 23 '14 at 17:11

2

Use GROUP_CONCAT :

SELECT text_group
     , GROUP_CONCAT(textfield SEPARATOR ' ') AS colA
FROM table
GROUP BY text_group

answered Sep 23 '14 at 17:11

potashin

43,297
11
81
105

1

_with_ the appropriate `GROUP BY text_group` – Michael Berkowski Sep 23 '14 at 17:12
if the group concat is gonna be very large dont forget to SET group_concat_max_len=100000000 – Logan Sep 23 '14 at 17:59

How to concatenate records based on a GROUP BY?

2 Answers2