Concatenate multiple MySQL rows into one field using GROUP_CONCAT

We can concatenate multiple MySQL rows into one field using GROUP_CONCAT function in mySQL query. There might be situations when you select multiple values from multiple tables and after all the joins you have got a lot of rows than you would like. We can useGROUP_CONCAT to obtain the same result with less numbers of rows.

The full syntax from MySQL documentation:

Concatenate multiple MySQL rows into one field

  • The DISTINCT clause is used to eliminate duplicate values in each row returned.
  • To sort values in the result, use the ORDER BY clause.
  • Add the DESC keyword to the name of the column you are sorting in descending order by in the ORDER BY clause.
  • The default separator between values in a group is comma (“,”).
  • To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values.
  • To eliminate the separator altogether, specify SEPARATOR ''.

Let’s  make it more clear with a real world example:

So for example you have the following 3 table representing shows in a theater, players and participation of players among those shows:

Now we wish to concatenate names of players in single field for each show using GROUP_CONCAT. So we created the query as per following:

Multiple mysql rows into one field

If you duplicate any (sid, pid) set from show_player table above and skip DISTINCT keyword from query then you will see duplicate entry of pid in pid_list column.

Now to get result in more readable format we can modify query and use GROUP_CONCAT and GROUP the results by sid:

Multiple rows into one field

In the query we have covered show with backtick in the query because this is a MySQL keyword. Read syntax error due to using a reserved word as a table or column name to know more in detail.

Note: There is a 1024 byte limit on result. It means the result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. To solve this, run this query before your query:

You can also change 2048 according to your needs.

I hope this little demo to concatenate multiple MySQL rows into one field using GROUP_CONCAT was helpful to you. If you have any questions or comments, feel free to use the comment form below.

You Might Interested In


    1. Amit Sonkhiya says:

      You can either use LEFT JOIN or NOT IN clause. Thought JOIN is more optimized.

      SELECT * FROM players WHERE pid NOT IN (SELECT pid FROM show_player)

  1. andrsi says:

    How can I select multiple values from a column from the left joined table using WHERE with AND for the same column name!?

    1. Amit Sonkhiya says:


      Use something like this:

      SELECT t1.col FROM table_name t1 LEFT JOIN table_name t2 ON t1.col = t2.col WHERE t1.some_col = ‘some_value’ AND t1.other_col = ‘other_value’


Leave a Reply

Enclose a code block like: <pre><code>Your Code Snippet</code></pre>.