Combined rows with same id in to one row keeping all data

pivot, sql

Solution

This is basically a `PIVOT` (you did not specify RDBMS) I am assuming MySQL and it does not have a `PIVOT` function so you will need to replicate this using an aggregate function with a `CASE` statement. This solution adds a `rownumber` to each row so you can determine how many `name` values you need to transform to columns.

If you know how many `name` values you are going to have you can hard-code the values:

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
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;

See SQL Fiddle with Demo

If you have an unknown number of values, then you can use a prepared statement to generate a dynamic version of this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when nameRn = ''',
      nameRn,
      ''' then name end) AS ',
      nameRn
    )
  ) INTO @sql
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;


SET @sql = CONCAT('SELECT plantid, ', @sql, ' 
                  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');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

Both will generate the same result:

| PLANTID | NAME1 |  NAME2 |  NAME3 |
-------------------------------------
|       1 |  tree |   rose | (null) |
|       2 |  bush | (null) | (null) |
|       3 |  tree |   bush |   rose |

Problem

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 I also want to save results to a new table

Original source