Search code examples
mysqlsqlgroup-concat

Mysql group_concat split by count

Is it possible to perform a group_concat in mysql and have some sort of group limit? thus allowing the group to be split over a number of result rows?

e.g.

if I have a table called num like so:

    val
    ---
    1
    2
    3
    4
    5
    ...

and I use the statement select group_concat(val) from num

I get the result

1,2,3,4,5,...

What I want to do is set a group limit of 2 and get the result

1,2
3,4
5,...

Now the use case I have is hundreds of thousands of values that I want to select as neat groups of about 500.

Is it possible?

Solution

  • You can use a user variable to create a row number, then group by dividing the row numbers by the group size.

    SELECT GROUP_CONCAT(val) AS vals
FROM (SELECT val, @rownum := @rownum + 1 AS rownum
      FROM (SELECT val FROM nums ORDER BY val) AS vals
      CROSS JOIN (SELECT @rownum := -1) AS vars) AS temp
GROUP BY FLOOR(rownum/500)