CODEWITHSUNDEEP
mysql
Yaroslav Andriychuk
Yaroslav Andriychuk

Reputation: 33

concatenate similar rows into one MYSQL

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

Upvotes: 1

Views: 30

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

you can use group concat 

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

Upvotes: 1

Ullas
Ullas

Reputation: 11556

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

Upvotes: 2

Related Questions