How to Concatenate Multiple MySQL Rows into One Field

Can I concatenate multiple MySQL rows into one field?

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

MySQL solution to concatenate multiple rows of text based on 2 matching IDs

You're after something like that no ?

SQL Fiddle

Query 1:

SELECT 
  `substance_id` , 
  `display_id`, 
  GROUP_CONCAT( `generated_text` SEPARATOR '<br>' ) as concact_text
from generated_text_tbl 
group by 
  `substance_id` , 
   `display_id`

Results:

| substance_id | display_id | concact_text |
|--------------|------------|--------------|
|           38 |         27 |          xyz |
|           54 |        139 |   foo<br>bar |
|         1933 |        139 |          baz |

Query 2:

SELECT 
  `substance_id` , 
  `display_id`, 
  GROUP_CONCAT( `generated_text` ORDER BY id DESC SEPARATOR '<br>') as concact_reverse_text
from generated_text_tbl 
group by 
  `substance_id` , 
   `display_id`

Results:

| substance_id | display_id | concact_reverse_text |
|--------------|------------|----------------------|
|           38 |         27 |                  xyz |
|           54 |        139 |           bar<br>foo |
|         1933 |        139 |                  baz |

Official Documentation : https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

How can i concatenate multiple MySQL rows into one field?

Test this:

SELECT category.id_category, category.id_category_type, 
CONCAT(category.label, '-', parents.label), category.id_parent  FROM category INNER JOIN
(SELECT id_category AS id, label FROM category WHERE id_parent = -1) AS parents
ON parents.id = category.id_parent WHERE  category.id_category_type < 3 
ORDER BY category.id_category_type;

Your data mixes "parents rows" and "child rows": when a register has id_parent = -1, then i consider is a parent row, and when id_parent <> -1 is child row.

You are requesting a result column joining "label" column from "childs rows" and "parents rows", then its necesary build a "parent table", that is achieved with this:

(SELECT id_category AS id, label FROM category WHERE id_parent = -1) AS parent

then you can use "parent" table like any other table and build your join query

How to merge a column from multiple rows into a single string

1. Coalesce simply allows to replace NULL with another value. It cannot solve your task.
2. + does not concatenate strings, it is arithmetic addition operator only.
3. If you need to concatenate values from a lot of rows into single value then you should use GROUP BY (maybe implicit) and GROUP_CONCAT():

SELECT GROUP_CONCAT([DISTINCT] name [ORDER BY name])
FROM cricketers;

If you do not need to remove duplicates then remove DISTINCT.

If you need to limit the amount of values concatenated then you may concatenate then remove excess values by:

SELECT SUBSTRING_INDEX(GROUP_CONCAT([DISTINCT] name [ORDER BY name]), ',', 20)
FROM cricketers;

or select needed rows amount in subquery:

SELECT GROUP_CONCAT(name [ORDER BY name])
FROM ( SELECT [DISTINCT] name
       FROM cricketers
       [ORDER BY name]
       LIMIT 20 ) subquery;

ORDER BY expression presence is strongly recommended - without them you will receive indefinite values ordering (and in the case of amount limitation - their selection).

---

Related Topics