2

How do I SELECT multiple rows of the same data (same table) as a single row?

Use case: I'm building a contacts management tool. Some people may have multiple email addresses and/or phone numbers. Instead of having to manually add yet another LEFT JOIN and return something such as the following:

//print_r($row1);
//email1, email2, email3, email4, phone1, phone2, phone3, phone4

I would prefer to have MariaDB return something visually along the lines of the following:

//print_r($row1);
//email, phone

//print_r($row1['email']);
//1@example.com,2@example.com,3@example.com

//print_r($row1['phone']);
//123-4567,456-7890,109-345

This would allow me to avoid static JOINs. How can this be accomplished using MariaDB?

A great example is SQL Server's STUFF function.

Dharman
  • 30,962
  • 25
  • 85
  • 135
John
  • 1
  • 13
  • 98
  • 177
  • Does this answer your question? [Can I concatenate multiple MySQL rows into one field?](https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) – philipxy Jul 07 '20 at 00:43

1 Answers1

4

The function I needed is group_concat().

Before

SELECT phone FROM contacts_phone;
  • 123
  • 456
  • 789

After

SELECT group_concat(phone) FROM contacts_phone;
  • 123,456,789
John
  • 1
  • 13
  • 98
  • 177
  • 2
    You could also specify the separator inside the `GROUP_CONCAT` if you don't want the results to be separated by commas. For example `SELECT GROUP_CONCAT(phone SEPARATOR ' ')` will return value as following `123 456 789` and `SELECT GROUP_CONCAT(phone SEPARATOR ';')` will return value as following `123;456;789` depending on your preference. – FanoFN Jun 11 '19 at 00:34
  • 1
    @tcadidot0 Nice! While I'm perfectly happy with the default delimiter being a comma it's nice to have the option to change it! Thank you very much! – John Jun 11 '19 at 00:46