sql query to merge a variable number of rows into one row

Question

My tables:

people
      name      person_id 
        Joe     1 
        Jack    2 

tags   
      tag_id    tag_name 
          1     blue 
          2     green 
          3     black 
          4     red

assignments
      person_id tag_id 
          1     1 
          1     2 
          1     3 
          1     4 
          2     2 
          2     4

What I want is the following result:

result

  name  tags 
  Joe   blue,green.black,red
  Jack  green,red

What is unique here is that the third table requires me to join the names of the tags first and I wan't those concatenated, not the raw ids...

possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — Nathan Tuggy, Aug 23 '15 at 02:00

asdf · Accepted Answer · 2015-08-23T02:05:34.880

3

Use the GROUP_CONCAT() method in conjunction with GROUP BY

SELECT name, GROUP_CONCAT(tag_id) FROM people GROUP BY name;

For the edited section, try this:

SELECT p.name, GROUP_CONCAT(t.tag_name)
    FROM people p
        JOIN assignments a ON p.person_id=a.person_id
        JOIN tags t ON t.tag_id=a.tag_id
    GROUP BY a.person_id;

edited Aug 23 '15 at 02:05

answered Aug 23 '15 at 01:44

asdf

2,927
2
21
42

@J.Doe If you could add another portion to your original question reflecting the new query I could try and help. – asdf Aug 23 '15 at 01:58
In general, it's good practice to GROUP BY the non-aggregated columns appearing in the SELECT – Strawberry Aug 23 '15 at 11:41

score 0 · Answer 2 · edited Aug 23 '15 at 02:20

0

SELECT p.name
, CONCAT_GROUP(t.tag_id SEPARATOR ',')
FROM tag t,  person p
WHERE t. tag_id = p. tag_id
GROUP BY p. name

I hope that this could help

edited Aug 23 '15 at 02:20

whereisSQL

638
2
13
23

answered Aug 23 '15 at 01:56

IBJ

31
3

I really wouldn't encourage the use of this archaic join syntax. – Strawberry Aug 23 '15 at 07:03

sql query to merge a variable number of rows into one row

2 Answers2