MySQL: Transform row data to columns

Question

I have this data in a cell:

1: 0100
2: 77042609291929541
3: 3100000
4: 01
7: 12014
11: 115
14: 11502
18: 5999
22: 0112
25: 015
41: 00083121
49: 7014

How can I transform these values into columns? E.g.

col_1 |_______col_2_______ | _ col_3 _ |col_4 |col_5 |.....col_127 |col_128 |
_____________________________________________________________________________

0100  |77042609291929541   | 3100000   |.............................

Answer 1

GROUP_CONCAT is what you are searching. https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Edit: Oups, read your question to fast, They are no easy way of doing what you ask, you may want to redesign your table. You will probably have to loop over your cell and use SUBSTRING_INDEX to first split by \n then cutting on your :.

I have no where to test so it's probably not working as it, but it's just to give you a start. (also this is only the first loop, but the inner one is quite the same.)

SET TEST = "1: 0100
2: 77042609291929541
3: 3100000
4: 01
7: 12014
11: 115
14: 11502
18: 5999
22: 0112
25: 015
41: 00083121
49: 7014";

SET len =  LENGTH(TEST);
SET startpos = 1;
REPEAT
        SET endpos = LOCATE('\n', TEST, startpos);
        SET item =  SUBSTR(TEST, startpos, endpos - startpos);

        IF item <> '' AND item IS NOT NULL THEN           
            print item;
        END IF;
        SET startpos = endpos + 1;
UNTIL startpos >= len END REPEAT;