Mysql - Concat fields based on value of field

Question

Given a table like this:

id     name     field
1      walt       a
2      hurley     b
3      jack       c
4      kate       a
5      sawyer     a
6      john       a
7      ben        b
8      miles      null
9      juliet     c
10     jacob      d
11     richard    null

How would you transfer it into this:

id     ids       names                      field
1      1,4,5,6   walt, kate, sawyer, john     a
2      2,7       hurley, ben                  b
3      8         miles                       null
4      3,9       jack, juliet                  c
5      10        jacob                        d
6      11        richard                     null

It needs to look at all the rows having the same field value. Then it needs to "merge" all other values based on equality of the field value. However if the field value is null, it should do nothing.

thought about that, but curious then, would you ever use mysql group_concat? — Björn
– Björn, Commented Feb 28, 2018 at 0:12
so, when would you? :) is this some general known "best practice advice" to keep selects simple and do data arranging via application code? maybe some good literature on that? — Björn
– Björn, Commented Feb 28, 2018 at 0:19
I might use it in combination with some other aggregation, and/or for certain kinds of ranking queries. I don't think I'm qualified to comment on best practice. — Strawberry
– Strawberry, Commented Feb 28, 2018 at 0:45

Bill Karwin · Accepted Answer · 2018-02-27 01:40:26Z

3

I got this to work:

mysql> set @x:= 1;

mysql> select group_concat(id) as ids, group_concat(name) as names, field 
from `a table like this` group by coalesce(field, @x:=@x+1);
+---------+-----------------------+-------+
| ids     | names                 | field |
+---------+-----------------------+-------+
| 8       | miles                 | NULL  |
| 11      | richard               | NULL  |
| 1,4,5,6 | walt,kate,sawyer,john | a     |
| 2,7     | hurley,ben            | b     |
| 3,9     | jack,juliet           | c     |
| 10      | jacob                 | d     |
+---------+-----------------------+-------+

Basically, I tricked the query into treating each NULL as a non-NULL value that increments each time we evaluate it, so each row with a NULL counts as a distinct group.

Re your comment:

You can also initialize a variable within the query like this:

select group_concat(id) as ids, group_concat(name) as names, field 
from (select @x:=1) AS _init
cross join `a table like this` 
group by coalesce(field, @x:=@x+1);

edited Feb 27, 2018 at 1:40

answered Feb 27, 2018 at 0:27

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Björn Over a year ago

Nice trick indeed! However unfortunately in my query I wont be able to set a @x variable like this.

Björn Over a year ago

Thank you for the updated answer. However something weird is happening. Sometimes when I run the query, the field value is NULL where it should have a value - e.g. the row with the names “hurley,ben” suddenly have a field value of NULL instead of "b". This only happens sometimes. So maybe it is some kind of race condition where @x does not increment fast enough? Will have to look into it... my real life table is a bit larger.

Uueerdo Over a year ago

@Hans are some of your field values numeric? It could be that @x ends up matching with some at random; you could try initializing @x to a value high (or low) enough, that you can guarantee now collisions with real values. (Another possibility is that there could be something weird happening with type coercion in the coalesce due to field being a string and @x being a number.)

Björn Over a year ago

the field values are indeed numeric. counting down (@x:=@x-1) instead up fixed the issue. many thanks!

Uueerdo · Accepted Answer · 2018-02-27 00:47:20Z

GROUP_CONCAT can be used to aggregate data from different rows into a concatenated string (as its name would suggest); it also supports and ORDER BY clause of it's own, so you want make doubly sure corresponding values end up in the same relative position of the list*.

SELECT MIN(id)
   , GROUP_CONCAT(id ORDER BY id)
   , GROUP_CONCAT(name ORDER BY id)
   , field
FROM theTable
WHERE field IS NOT NULL
GROUP BY field
UNION 
SELECT id, id, name, field
FROM theTable
WHERE field IS NULL
;

* aggregate functions ignore NULL values, so technically if either id or name contain NULL, the lists will become misaligned; this could be remedied with something like GROUP_CONCAT(IFNULL(concatenated_value, '[null]') ORDER BY ordering_value)

Collectives™ on Stack Overflow

Mysql - Concat fields based on value of field

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related