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

Question

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

Will `select group_concat(name) from new_plantsname group by plantid` do? (Assuming MySQL) — Terje D., Nov 15 '12 at 18:01
Is it useful to find max ID for the plants and then use PIVOT? — Maryam Arshi, Nov 15 '12 at 18:05

score 4 · Accepted Answer · answered Nov 15 '12 at 18:04

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 |

thanks this is great would vote answer up but new to site so cant yet — zaratjlc, Nov 15 '12 at 19:20
I am doing this in wamp phpmyadmin and getting an error: #1243 - Unknown prepared statement handler (stmt) given to EXECUTE — zaratjlc, Nov 16 '12 at 10:47
@zaratjlc I am unable to reproduce the error you say you are getting. — Taryn, Nov 16 '12 at 13:05
@zaratjlc I am unsure why you unaccepted this answer and then posted a new question with your issue. — Taryn, Nov 16 '12 at 13:14
Sorry a friend told me if it didnt work to unaccept answer as it closes the question I am getting a error with the code you showed it is: #1243 - Unknown prepared statement handler (stmt) given to EXECUTE — zaratjlc, Nov 16 '12 at 13:29
@zaratjlc The problem is now you have opened another question with the same issue causing a duplicate. So you will need to either accept this one, or delete the other one. — Taryn, Nov 16 '12 at 13:30
I am getting the error in the wamp phpmyadmin sql generator I am using wampserver version 2.2 php version 5.4.3 — zaratjlc, Nov 16 '12 at 13:31
@zaratjlc I will see if I can recreate the issue and post an answer on your other question. — Taryn, Nov 16 '12 at 13:32
Thanks the table does have other columns but I only need plantid and names pulled through to a new table — zaratjlc, Nov 16 '12 at 13:35
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/19650/discussion-between-zaratjlc-and-bluefeet) — zaratjlc, Nov 16 '12 at 18:22

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

1 Answers1

Linked