1

I have a database with email and phone columns. One email can have several phones, which results in multiple rows with same email but different phone numbers.

I want to query all emails with it's phones grouped in one single column.

Example

Convert from this

11111  mail@mail.com
22222  mail@mail.com  
33333  mail@mail.com
44444  mail@mail.com

To this

mail@mail.com  11111, 22222, 33333, 44444

Thanks!

Shadow
  • 33,525
  • 10
  • 51
  • 64
Roberto Iglesias
  • 57
  • 1
  • 6
  • I suppose you're trying to transform a numeric value to a single string. Try checking out aggregate functions that can work with strings here >> https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html. – lemon Oct 15 '22 at 15:20

2 Answers2

4

group_concat to the rescue:

SELECT   email, GROUP_CONCAT(other ORDER BY other ASC SEPARATOR ', ') 
FROM     mytable
GROUP BY email
Mureinik
  • 297,002
  • 52
  • 306
  • 350
1

I found it

select email,GROUP_CONCAT(tel1,',',tel2) as phones from table group by email;

Thanks!

Roberto Iglesias
  • 57
  • 1
  • 6