CODEWITHSUNDEEP
mysqlsqlstringconcatenationworkbench
Joe
Joe

Reputation: 340

How to compress multiple rows with concat

Say I have a table COG in a database and each COG in the COG table can be linked to one or more organisms in a second table. If I just join these, I end up duplicating the COG numerous times for each organism.

I would like to generate a query that would instead, put each organism linked to a given COG into a single string using concat_ws() (i.e. CSV format).

I tried using: GROUP BY concat_ws(',', organism.name)

But that didn't work.

Any suggestions?

Upvotes: 1

Views: 167

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

You're looking for the GROUP_CONCAT function:

SELECT c.ID, GROUP_CONCAT(o.name)
    FROM COG c
        INNER JOIN organism o
            ON c.ID = o.COG_ID
    GROUP BY c.ID;

Upvotes: 4

Related Questions