MySQL: concatenating strings from multiple rows in one table-field

If you have the need to concatenate multiple strings from different rows into one single field via a sql-query, MySQL gives you the useful GROUP_CONCAT()-function.

At first we create a table and insert some values with ID as our designated “group by”-column:

mysql> create table t1 (id int, txt varchar(3));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,'ABC'),(2,'def'),(1,'GHI'),(2,'jkl'),(1,'mno'),(2,'pqr');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| id   | txt  |
+------+------+
|    1 | ABC  |
|    2 | def  |
|    1 | GHI  |
|    2 | jkl  |
|    1 | mno  |
|    2 | pqr  |
+------+------+
6 rows in set (0.00 sec)

mysql>

Then we’ll do a SELECT on that table and concat the group-specific strings with GROUP_CONCAT():

mysql> select id, group_concat(txt) from t1 group by id;
+------+-------------------+
| id   | group_concat(txt) |
+------+-------------------+
|    1 | ABC,GHI,mno       |
|    2 | def,jkl,pqr       |
+------+-------------------+
2 rows in set (0.01 sec)

mysql>

We could also define the string-separator with the SEPARATOR-keyword:

mysql> select id, group_concat(txt SEPARATOR ':') from t1 group by id;
+------+---------------------------------+
| id   | group_concat(txt SEPARATOR ':') |
+------+---------------------------------+
|    1 | ABC:GHI:mno                     |
|    2 | def:jkl:pqr                     |
+------+---------------------------------+
2 rows in set (0.00 sec)

mysql>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: