Is there a better way to split MySQL GROUP_CONCAT output into multiple rows? for example say after every six (6) record

Question

I have successfully achieved this scenario below:

SELECT GROUP_CONCAT(coursecode) 
    FROM (
    SELECT coursecode FROM Table18
    WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39

    UNION  

    SELECT coursecode FROM Table17
    WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39
) s

WHERE coursecode 
NOT IN (
    SELECT coursecode 
    FROM Table18
    WHERE regno = 'StudentXYZ' 
    AND (ca_score + exam_score) >= 40

    UNION

    SELECT coursecode 
    FROM Table17
    WHERE regno = 'StudentXYZ' 
    AND (ca_score + exam_score) >= 40
    )

This works great! Here is the sample result below:

+----------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(coursecode)                                                                                 |
+----------------------------------------------------------------------------------------------------------+
| EDU222,EDU497,POS302,POS405,POS420,EDU224,EDU311,EDU312,EDU313,GST304,GST305,POS304,POS305,POS308,POS309 |
+----------------------------------------------------------------------------------------------------------+

But this is what I desire to achieve:

+-------------------------------------------------+
| GROUP_CONCAT(coursecode)                        |
+-------------------------------------------------+
| EDU222, EDU497, POS302, POS405, POS420, EDU224, |
| EDU311, EDU312, EDU313, GST304, GST305, POS304, |
| POS305, POS308, POS309                          |
+-------------------------------------------------+

Any suggestions? or better-way to solve this challenge?

Answer 1

You can do something like the following (supporting MySQL 5.7 / MariaDB < 10.2):

SELECT GROUP_CONCAT(coursecode)
FROM (
    SELECT coursecode, @gn:=@gn+1, CEIL(@gn / 6) gn
    FROM (
        SELECT coursecode FROM Table18
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39

        UNION  

        SELECT coursecode FROM Table17
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39
    ) s, (SELECT @gn:=0)gn
    WHERE coursecode NOT IN (
        SELECT coursecode 
        FROM Table18
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) >= 40

        UNION

        SELECT coursecode 
        FROM Table17
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) >= 40
    )
) group_view GROUP BY group_view.gn

demo on dbfiddle.uk

Since MySQL 8.0 / MariaDB 10.2 you can use the same logic, but with ROW_NUMBER instead of the variable. So your query can look like this:

SELECT GROUP_CONCAT(coursecode)
FROM (
    SELECT coursecode, CEIL(ROW_NUMBER() OVER (ORDER BY code ASC) / 6) gn
    FROM (
        SELECT coursecode FROM Table18
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39

        UNION  

        SELECT coursecode FROM Table17
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) <= 39
    ) s
    WHERE coursecode NOT IN (
        SELECT coursecode 
        FROM Table18
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) >= 40

        UNION

        SELECT coursecode 
        FROM Table17
        WHERE regno = 'StudentXYZ' AND (ca_score + exam_score) >= 40
    )
) group_view GROUP BY group_view.gn

demo on dbfiddle.uk