CSV formatted GROUP_CONCAT in MySQL

Question

Let's say I have a Table A that I want to transform into Table B.

The values in Table B should always be a CSV formated text with the same number of fields.

First, I need to know what is the largest number of values that a given category handles (in this case, 3 values in category 1, 2 and 4);

Secondly I also need to use that variable to "add" empty fields(",") to the end of the GROUP_CONCAT when a category has "missing" values.

I need this to have a "consistent" CSV in each cell. The application I'm using to process this data doesn't interpret well CSVs with different column number by row...

Table A

+----+----------+-------+
| id | category | value |
+----+----------+-------+
| 1  |    1     |   a   |
| 2  |    1     |   b   |
| 3  |    1     |   c   |
| 4  |    2     |   d   |
| 5  |    2     |   e   |
| 6  |    2     |   f   |
| 7  |    3     |   g   |
| 8  |    3     |   h   |
| 9  |    4     |   i   |
| 10 |    4     |   j   |
| 11 |    4     |   k   |
| 12 |    5     |   l   |
+----+----------+-------+

Table B

+--------------+---------------------+
| id(category) | value(group_concat) |
+--------------+---------------------+
|      1       |        a,b,c        |
|      2       |        d,e,f        |
|      3       |        g,h,         |
|      4       |        i,j,k        |
|      5       |        l,,          |
+--------------+---------------------+

EDITED (SQLFiddle):

http://sqlfiddle.com/#!2/825f8

thanks for the sqlfiddle. it does help! – caitriona Oct 10 '12 at 14:13 — caitriona, Oct 10 '12 at 14:13

score 2 · Accepted Answer · answered Oct 10 '12 at 14:13

first, to get the largest number of values that a given category handles:

select count(category) from tableA group by category order by count(category) desc limit 1;

second, to add empty fields(",") to the end of the GROUP_CONCAT when a category has "missing" values.

i created a function called unify_length to help do this.

this is the function:

delimiter $$

CREATE FUNCTION `unify_length`(csv_list CHAR(255), length INT) RETURNS char(255)
    DETERMINISTIC
BEGIN        
        WHILE ((SELECT LENGTH(csv_list) - LENGTH(REPLACE(csv_list, ',', ''))) < length-1) DO /* count the number of occurrances in a string*/
            SET csv_list = CONCAT(csv_list, ',');        
        END WHILE;

        RETURN csv_list;
END$$

and this is the function call:

select category, unify_length(GROUP_CONCAT(value), length) from tablea group by category;

where length is what was returned from the first query.

CSV formatted GROUP_CONCAT in MySQL

EDITED (SQLFiddle):

1 Answers1