CODEWITHSUNDEEP
phpmysqlstringconcatenationaggregate
Haradzieniec
Haradzieniec

Reputation: 9338

How to concatenate strings into a new column based on GROUP BY

I have a similar question to How to use GROUP BY to concatenate strings in MySQL? , however for this example for mytable table

id    string    aggr
1          A    NULL
1          B    NULL
2          F    NULL

The difference is I need to update the table to get this results:

id    string   aggr
1          A   A|B|
1          B   A|B|
5          C   C|E|C|
5          E   C|E|C|
5          C   C|E|C|
2          F   F|

As result the same id we have the same newcolumn values.

It is absolutely fine to add a delimiter |at the very end of the string. That way I can even faster count how many "items" are in the newcolumn without adding +1 because of the absense at the very end (just in between). Also I won't care about validation (if I have a pipe right before) when appending another part or two into aggr column.

Thank you.

Upvotes: 0

Views: 97

Answers (2)

Guillaume Sainthillier
Guillaume Sainthillier

Reputation: 1685

You can try this query : 

UPDATE my_table t
SET aggr = (
    SELECT * FROM (
        SELECT CONCAT(GROUP_CONCAT(t2.string SEPARATOR '|'), '|')
        FROM my_table t2
        WHERE t2.id = t.id
   ) AS X
)

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133400

You could a group_concat joined on original table 

select a.id, a.string ,  b.aggr
from my_table a
inner join  (
  select id, group_concat(string SEPARATOR '|') as aggr
  from my_table 
  group by  id 
) b on a.id = b.id

Upvotes: 1

Related Questions