How can i concatenate multiple MySQL rows into one field?

Question

Using MySQL, I can do something like:

SELECT c.id_category, c.id_category_type, c.label, c.id_parent FROM category c WHERE c.id_category_type < 3 ORDER BY c.id_category_type;

The result is:

id_category |id_category_type |label |id_parent | ------------|-----------------|----------------------|----------| 1 |1 |Demande d'information |-1 | 2 |1 |Réclamation |-1 | 18 |1 |TEST |-1 | 3 |2 |Autre |1 | 4 |2 |Mairie |1 | 5 |2 |Stationnement |1 | 6 |2 |Autre |2 | 7 |2 |Familles |2 | 19 |2 |TESTDOMAINE |18 |

but instead I just want concat row who i have a id_category with id_parent

Example:

Autre-Demande d'information Mairie-Demande d'information Stationnement-Demande d'information Autre-Réclamation Familles-Réclamation TESTDOMAINE-TEST

I've looked for a function on MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets, so does anyone here know how to do this?

Ing. Gerardo Sánchez · Accepted Answer · 2017-12-23 00:26:39Z

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

Collectives™ on Stack Overflow

How can i concatenate multiple MySQL rows into one field?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related