MYSQL: delete empty cells

Question

I have a table, for example, "mytable". It has several columns: col1, col2, col3. There is some data in it, placed like that:

id  col1    col2    col3
1   aasdas          
2           acxvxc  
3                   dgdgdff
4   hfgdgh          
5           jmjkh   
6                   dfgdfgd
7   bcbxcb  

For some reason I need table, formatted like that:

id  col1    col2    col3
1   aasdas  acxvxc  dgdgdff 
2   hfgdgh  jmjkh   dfgdfgd 
3   bcbxcb      

I thought I could do something like INSERT INTO "mytable" ("col1") VALUES (SELECT "col1" FROM "mytable" WHERE "col1"<>'') and etc, but of course it doesn't work.

Any ideas? =)

Answer 1

SELECT  MAX(col1), MAX(col2), MAX(col3)
FROM    mytable
GROUP BY
        (id - 1) DIV 3

Answer 2

hmm i think the modulo won't work because it is repeating..
e.g. 0, 1, 2, 0, 1, 2, ...
You will need:

GROUP BY (id - 1) / 3

So you get: 0, 0, 0, 1, 1, 1, 2, 2, 2, ...
And 3 consecutive rows are selected and merged.

But I am not quite sure if it is possible to use arithmetic operations in GROUP BY clause because normally you can only group by a single column

[edit]
I have tested some things and for me (mysql) the division / modulo in GROUP BY won't work.
But I have something that seems to work for me:

SELECT `id`, MAX(`column1`) AS `col1`, MAX(`column2`) AS `col2`, MAX(`column3`) AS `col3` FROM
( SELECT ROUND((`id`-2) / 3, 0) AS `id`, MAX(`col1`) AS `column1`, MAX(`col2`) AS `column2`, MAX(`col3`) AS `column3` FROM `test` GROUP BY `id` ) AS `temp`
GROUP BY `id`

Have Fun :)