How to use MySQL group_concat() function in more than one column

Question

I have this query

SELECT de.id as id_evento,de.evento, dp.id as id_participante,
        dp.nombre as participante,pn.participacion,
        pn.id as id_participacion
                FROM difusion_evento de,difusion_evento_participante dep,difusion_participante dp,
                      difusion_participacion pn
                WHERE de.id=dep.difusion_evento_id
                  AND dep.difusion_participante_id=dp.id
                  AND dep.difusion_participacion_id=pn.id
                  AND de.id=5
                  ORDER BY 3,6

And the results are:

id_evento    evento  id_participante     participante    participacion   id_participacion
5    5TO SEMINARIO PERMANENTE…  8    Name1   participante   2
5    5TO SEMINARIO PERMANENTE…  8    Name1   comentarista   4
5    5TO SEMINARIO PERMANENTE…  9    Name2   participante   2
5    5TO SEMINARIO PERMANENTE…  10   Name3   participante   2

But i want the results to appear like the following:

id_evento    evento  id_participante     participante    participacion   id_participacion
5    5TO SEMINARIO PERMANENTE…  8    Name1   participante,comentarista  2, 4
5    5TO SEMINARIO PERMANENTE…  9    Name2   participante   2
5    5TO SEMINARIO PERMANENTE…  10   Name3   participante   2

That is to say, i want the id_participante column to appear DISTINCT and the two columns participacionand id_participacion to be grouped. For example, Name1 appears two times and i want to group its results in one column.

I have tried GROUP_CONCAT() wth no luck, any ideas to achieve this?

Solved Thanks to the first answer, i was missing, indeed, the GROUP BY id_participante

So the working query is:

SELECT de.id as id_evento,de.evento, dp.id as id_participante,
    dp.nombre as participante,GROUP_CONCAT(pn.participacion SEPARATOR ', '),
    GROUP_CONCAT(pn.id SEPARATOR ', ') as id_participacion
            FROM difusion_evento de,difusion_evento_participante dep,difusion_participante dp,
                  difusion_participacion pn
            WHERE de.id=dep.difusion_evento_id
              AND dep.difusion_participante_id=dp.id
              AND dep.difusion_participacion_id=pn.id
              AND de.id=5
            GROUP BY id_participante
            ORDER BY 3,6

what's the matter with `select group_concat(participacion), group_concat(id_participacion)`? as long as your `group by` clause is set correct, there's no reason that shouldn't work. — Marc B, Jun 15 '15 at 14:52
possible duplicate with http://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length?rq=1 — , Jun 15 '15 at 14:53

score 1 · Accepted Answer · answered Jun 15 '15 at 14:55

Use GROUP BY id_participante to group by id_participante.

You will have distinct participant ids on separate lines, and the other fields are grouped together per participant id.

SELECT de.id as id_evento,de.evento, dp.id as id_participante,
    dp.nombre as participante,pn.participacion,
    pn.id as id_participacion
            FROM difusion_evento de,difusion_evento_participante dep,difusion_participante dp,
                  difusion_participacion pn
            WHERE de.id=dep.difusion_evento_id
              AND dep.difusion_participante_id=dp.id
              AND dep.difusion_participacion_id=pn.id
              AND de.id=5
            GROUP BY id_participante
            ORDER BY 3,6

How to use MySQL group_concat() function in more than one column

1 Answers1