Combine multiple rows into single row using sqlite database

See more:

Java

SQLite

i have this table

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

please help me sqlite code to produce this i try this by there is an error


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

What I have tried:

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;