MYSQL group concat limit separator-CodePudding

I have the following query and it works fine for me. It shows me the descriptions up to a maximum of 10 separated by commas. But I would like that if there were more than 10, at the end I would put ', ...' to indicate that there are more fields that have been hidden.

Is there any way to perform this action?

Thanks in advice

SELECT ad.domain, GROUP_CONCAT(g.description LIMIT 10)
FROM allowed_domains ad, allowed_domain_groups adg, groups g
WHERE ad.id = adg.id_allowed_domain
AND adg.id_group = g.id
GROUP BY ad.id
ORDER BY GROUP_CONCAT(g.description) DESC

CodePudding user response：

You can do this using an IF statement like this:

SELECT
    ad.domain,
    IF(COUNT(*) > 10, CONCAT(GROUP_CONCAT(g.description LIMIT 10), '...'), GROUP_CONCAT(g.description LIMIT 10)) 
FROM
    allowed_domains ad,
    allowed_domain_groups adg,
    groups g 
WHERE
    ad.id = adg.id_allowed_domain 
    AND adg.id_group = g.id 
GROUP BY
    ad.id 
ORDER BY
    GROUP_CONCAT(g.description) DESC

CodePudding user response：

This can be done by using substring_index:

Try:

SELECT ad.domain, 
       substring_index(GROUP_CONCAT(g.description SEPARATOR ','), ',', 10)
FROM allowed_domains ad, allowed_domain_groups adg, groups g
WHERE ad.id = adg.id_allowed_domain
AND adg.id_group = g.id
GROUP BY ad.id
ORDER BY GROUP_CONCAT(g.description) DESC ;