CODEWITHSUNDEEP
mysqlconcatenation
Björn
Björn

Reputation: 13207

Mysql - Concat fields based on value of field

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.

Upvotes: 1

Views: 93

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

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);

Upvotes: 3

Uueerdo
Uueerdo

Reputation: 15941

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)

Upvotes: 2

Related Questions