Mysql combining data from columns to row

Question

The rough structure of the mysql table is like this Person Name, Mobile Number1, Mob2, Mob3, Mob4.

What I am trying to do is: For example, if the table has 4 records for the same person like:

Person Mob1 Mob2 Mob3 Mob4
John   123  
John   435
John   324
John   432

I need to combine the four records into one like:

Person  Mob1 Mob2 Mob3 Mob4
John    123  435  324  433

Is it possible to do this using a mysql query in phpMyAdmin? I know it is possible using a php script, but the table is quite huge :close to 500Mb (Nearly a million records.), so a script would be extremely slow/take very long time to complete.

Answer 1

I think you'll need to use a stored procedure, check these other posts for (possibly) helpful info:

• split keywords for post php mysql
• MySQL procedure to load data from staging table to other tables. Need to split up multivalue field in the process
• How to expand comma-separated field into multiple rows in MySQL

Or you could try to do it with a function and a group_concat():

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
 LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
 delim, '');

And then the select query would be:

SELECT
  tmp.id,
  SPLIT_STR(tmp.mobs, ',', 1) as mob1,
  SPLIT_STR(tmp.mobs, ',', 2) as mob2,
  SPLIT_STR(tmp.mobs, ',', 3) as mob3,
  SPLIT_STR(tmp.mobs, ',', 4) as mob4
  FROM (
    SELECT
      id,
      GROUP_CONCAT(mob1) as mobs
    FROM person
    GROUP BY name
  ) tmp

Of course, then you'd have to integrate that into an UPDATE statement, but I'll let you try that on your own. (btw, got the FUNCTION from here).