Combined rows with the same identifier in one row, storing all data

Question

Can anyone help? I have a table with several entries for plants. A plant can have several 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 3,000 entries

what I want is to combine the records with the same plant and show different names in different columns:

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

etc.

Can anyone help me in this matter. I also want to save the results in a new table

Thank you in advance

Answer 1

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

If you know how many name values ​​you will 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, you can use the prepared statement to create 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 |