Allow duplicated values at mysql group_concat operation

Question

I, use the following sql statement:

SELECT  group_concat(name SEPARATOR '') FROM words WHERE id in (1,2,3,1,2)

The problem at this statement is, that only names from 1,2,3 will concatenated but I need the name concatenation from 1,2,3,1,2 in exact that order.

So I will allow duplicated values, how can I do that?

Answer 1

Assuming is the id column non unique? If so and there are multiple rows what you have would work.

[test]> create table words ( id int, name varchar(200));
Query OK, 0 rows affected (0.00 sec)

[test]> insert into words (id,name) values (1,'a'),(2,'b'),(3,'c'),(2,'b'),(3,'c');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

[test]> SELECT  group_concat(name ) FROM words WHERE id in (1,2,3) ;
+---------------------+
| group_concat(name ) |
+---------------------+
| a,b,c,b,c           |
+---------------------+
1 row in set (0.00 sec)

Specifying an identifier in a where in list multiple times will not include matches for each specification. Further, as you see in my example specifying each one time still results in each match from the insert to be returned.

UPDATE After clarification:

If your id column is uninque you aren't going to get multiple copies of the rows to be returned with a simple query like that. doing a UNION ALL

Representing each set of duplicates uniquely will work

 [test]> select group_concat(name) from (select name from words where id in (1,2,3) union all select name from words where id in (1,2)) a;
+--------------------+
| group_concat(name) |
+--------------------+
| a,b,c,a,b          |
+--------------------+
1 row in set (0.00 sec)