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.

What do you intend to do with this data once you have pivoted it? — eggyal, Jan 10 '13 at 11:47
Is there a person_id column, or is it that names are unique. If there are a million rows, it is unlikely that there is only one "John Smith" — Bohemian, Jan 10 '13 at 21:29
reg the no of columns, there are like 20 columns. Reg what I am planning to do it after pivoting it, pivoting the data into a single record, then deleting other records which have empty mob2, mob3, mob4. Reg person_id yes, there is a IC for each person, so I can uniquely identify each person. — A programmer, Jan 11 '13 at 02:03
eggyal thanks so much for 'pivot' word. I didnt know this was called pivot operation... — A programmer, Jan 12 '13 at 12:49

score 0 · Accepted Answer · edited May 23 '17 at 12:18

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

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).

Thanks for showing me how to use GROUP BY, based my query on it, created a temporary table and voila the mysql query ripped blazing fast to do the job. Thank you so much. — A programmer, Jan 21 '13 at 02:15

Mysql combining data from columns to row

1 Answers1