concatenate similar rows into one MYSQL

Question

i have for example 40 rows like

|name | type | addr_index | numbers |
 - - - - - - - - - - - - - - - -
 john  1       46001       1,2,3 
 doe   1       67002       1,2,3 
 john  2       67002       10,22,34
 john  2       67002       7,4,3,2

How can i select rows with same data(name,type,addr_index), and concatenate numbers column to one row? like

|name | type | addr_index | numbers |
 - - - - - - - - - - - - - - - -
 john  1       46001       1,2,3 
 doe   1       67002       1,2,3 
 john  2       67002       1,2,3,10,22,34

https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field?rq=1 — Tiago Carreira, Feb 12 '18 at 15:07

score 2 · Accepted Answer · answered Feb 12 '18 at 15:07

2

Use GROUP_CONCAT

Syntax

GROUP_CONCAT([DISTINCT] expr [,expr ...]
         [ORDER BY {unsigned_integer | col_name | expr}
             [ASC | DESC] [,col_name ...]]
         [SEPARATOR str_val])

By default the separator is ,. If you need another separator apart from comma, then you can give it by,

GROUP_CONCAT(expr separator 'char');

Query

SELECT `name`, `type`, `addr_index `,
GROUP_CONCAT(`numbers`)
FROM `your_table_name`
GROUP BY `name`, `type`, `addr_index `;

answered Feb 12 '18 at 15:07

Ullas

11,450
4
33
50

what if name or type is different? i need compare first name type, addr_index and then concatenate data that mathed – Yaroslav Andriychuk Feb 12 '18 at 15:30

score 1 · Answer 2 · answered Feb 12 '18 at 15:07

1

you can use group concat

select name, type , addr_index , group_concat( numbers )
from my_table 
group by name, type , addr_index

answered Feb 12 '18 at 15:07

ScaisEdge

131,976
10
91
107

concatenate similar rows into one MYSQL

2 Answers2