CODEWITHSUNDEEP
mysqlconcatenationgroup-concat
Pandy Legend
Pandy Legend

Reputation: 1111

MySQL GROUP_CONCAT multiple fields

I'm probably having a no-brain moment.

I want to return a series of numbers using GROUP_CONCAT from two fields in my database. I have done this so far using the following:

SELECT t_id,
CONCAT(GROUP_CONCAT(DISTINCT s_id),',',IFNULL(GROUP_CONCAT(DISTINCT i_id),'')) AS all_ids
FROM mytable GROUP BY t_id

This works fine but if i_id is NULL then of course I get an unnecessary comma. Is there a better way to do this so I don't end up with a comma at the end if i_id is NULL?

Upvotes: 5

Views: 20002

Answers (1)

Omesh
Omesh

Reputation: 29061

You need to use CONCAT_WS to avoid extra comma for NULL values, try this:

SELECT t_id,
       CONCAT_WS(',', GROUP_CONCAT(DISTINCT s_id),
                 GROUP_CONCAT(DISTINCT i_id)) AS all_ids
FROM mytable
GROUP BY t_id;

Upvotes: 14

Related Questions