MySQL - Query combine rows with same id and keep all entry for that id but as one record

Question

I have been working on a table in a mysql database being hold locally on a wamp server I am using the phpmyadmin area with in wamp to run the querys. I am trying to get the data to do the following:

Can anyone help I have a table with a number of records for plants. A plant can have a number of names the table shows this as different records. The table is called new_plantsname

plantid name
1       tree
1       rose
2       bush
3       tree
3       bush
3       rose

this continues for over 3000 records

what i want is it to combined records with same plantid and show the different names in different columns:

plantid name1 name2 name3 ...
1       tree  rose  NULL
2       shrub NULL  NULL
3       tree  rose  bush 

etc

From a glance I believe a plant has no more than 4 names.

Can one help me the query to do this. I also want to save results to a new table

Someone has given me the following for the answer:

select plantid,
  max(case when nameRn = 'name1' then name end) Name1,
  max(case when nameRn = 'name2' then name end) Name2,
  max(case when nameRn = 'name3' then name end) Name3,
  max(case when nameRn = 'name4' then name end) Name4
from
(
  select plantid, name,
      concat('name', @num := if(@plantid = `plantid`, @num + 1, 1)) as nameRn,
      @plantid := `plantid` as dummy
  from
  (
    select plantid, name, @rn:=@rn+1 overall_row_num
    from yourtable, (SELECT @rn:=0) r
  ) x
  order by plantid, overall_row_num
) src
group by plantid;

This seem to work while no errors but it didnt combine the records it only kept the name of the first record with the id not the rest. DATA USED:

plantid     name
    1       tree
    1       rose
    2       tree
    3       rose
    3       bush
    3       rose

RESULTS:

Can anyone help

Answer 1

The problem is that MySQL does not have a good way of enumerating rows. The use of the constant is not guaranteed to work, alas, according to the MySQL documentation. It often does work, but it can also be problematic.

I would suggest that you concatenate the names together into a single field. The result would look like:

1     tree,rose
2     tree
3     tree,bush,rose

Using the SQL:

select plantid, group_concat(name separator ',')
from t
group by plantid

If you really wanted the names in separate columns, two options come to mind. One is to use the results from above and then parse the result into separate strings. The other alternative is to use a self-join and aggregation to calculate a sequential number, like this:

select p.plantid, p.name, count(*) as seqnum
from t p left outer join
     t pprev
     on p.plantid = pprev.plantid and
        p.name >= pprev.name
group by p.plantid, p.name

And use this as the subquery.