MySQL Group By Concatenate

Question

I have two SQL tables. customer and tag joined on the customer.id=tag.attach_id

customer
+------+-------------+--------------+
|  id  |   name      |   email      |
|  9   |   Alan      |  alan@me.com |
+------+-------------+--------------+

tag
+------+-------------+--------------+
| id   | attach_id   | content      |
| 1    |   9         | alan-tag     |
| 2    |   9         | second-tag   |
+------+-------------+--------------+

I want to output this:

+-------+-----------------+-----------------------+
| name  |     email       | content               |
+-------+-----------------+-----------------------+
| alan  | alan@me.com     | alan-tag, second-tag  |
+-------+-----------------+-----------------------+

Here's my best attempt at SQL for this:

SELECT customer.name, customer.email, tag.content
FROM customer
INNER JOIN tag
ON customer.id=tag.attach_id
GROUP BY customer.id,tag.content;

Is this even possible without first processing the data in another language like PHP?

Possible duplicate of [How to use GROUP BY to concatenate strings in MySQL?](https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) — Uueerdo, Feb 15 '18 at 17:53
Use group_concat in mysql itself https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php — Sumesh TG, Feb 15 '18 at 17:53

score 2 · Accepted Answer · edited Feb 15 '18 at 18:30

2

Yes you need to use GROUP_CONCAT as suggested by others on comment area, more specifically (exactly) your query is

SELECT `name`, email, GROUP_CONCAT(tag.content SEPARATOR ', ') as content
FROM
customer
INNER JOIN tag ON customer.id = tag.attach_id
GROUP BY customer.id

This Query will give you the exact result that you have osted on your post

edited Feb 15 '18 at 18:30

answered Feb 15 '18 at 18:02

BetaDev

4,516
3
21
47

1

Thank you all. I got to `SELECT tag.attach_id, GROUP_CONCAT(tag.content SEPARATOR ',') FROM tag GROUP BY tag.attach_id;` with the other help on here but this was the cherry on the cake. – Ian Kitson Feb 15 '18 at 18:10

praveen muppala · Answer 2 · 2018-02-15T18:09:00.173

0

You can use listagg if you are using sql

select c.name,email,listagg(content,',') within group (order by c.name) "content"
from customer c, tag t
where c.id = t.attach_id
group by c.name,email

edited Feb 15 '18 at 18:09

answered Feb 15 '18 at 18:02

praveen muppala

177
7

Will give you the error message, check your query and update, try to run and see – BetaDev Feb 15 '18 at 18:06
1

Thank you. Updated my query.. It was a typo while thinking. :) – praveen muppala Feb 15 '18 at 18:09
`LISTAGG` is Oracle, not MySQL, no? – kchason Feb 15 '18 at 19:55

score 0 · Answer 3 · edited Feb 15 '18 at 18:21

0

Use this:

SELECT customer.name, customer.email, GROUP_CONCAT(tag.content SEPARATOR ', ') as content
FROM customer
INNER JOIN tag
ON customer.id=tag.attach_id
GROUP BY customer.id;

edited Feb 15 '18 at 18:21

BetaDev

4,516
3
21
47

answered Feb 15 '18 at 18:02

vpalade

1,427
1
16
20

MySQL Group By Concatenate

3 Answers3