MySQL GROUP_CONCAT multiple fields

Question

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?

Omesh · Accepted Answer · 2012-10-08 12:44:53Z

14

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;

edited Oct 8, 2012 at 12:44

answered Oct 8, 2012 at 12:32

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pandy Legend Over a year ago

That would replace any double commas (,,) with a single one (,) but that isn't my problem. If the i_id is null I end up with a result such a 2,6,8, when what I need is 2,6,8

Christophe Vidal Over a year ago

The problem is that if there are duplicates between the 2 fields (s_id and i_id), the distinct on each separate field is not going to help

Collectives™ on Stack Overflow

MySQL GROUP_CONCAT multiple fields

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related