15,123,367 members
See more:
i have this table

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

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;
Posted
Updated 12-Sep-19 21:32pm
Richard MacCutchan 12-Sep-19 11:11am

Please, please, please, tell us what the error is. And show which line of code it occurs on.
ZurdoDev 12-Sep-19 11:29am

Google how to convert rows into columns. Or the other approach would be to concatenate rows together.
Mohamed iki 12-Sep-19 13:38pm

thanks

## Solution 1

Maciej Los 13-Sep-19 5:28am

5ed!

## Solution 2

Take a look at the GROUP_CONCAT function: SQLite group_concat() function - w3resource[^]
v2
Maciej Los 13-Sep-19 5:28am

5ed!

## Solution 3

`"select  id,max( case when name = 'tree' then name end) as name1,max(case when name = 'rose' then name end ) as name2 from table_name group by id";`

Top Experts
Last 24hrsThis month
 CPallini 160 OriginalGriff 145 Richard MacCutchan 105 CHill60 50 Gerry Schmitz 45
 OriginalGriff 990 Richard MacCutchan 415 Richard Deeming 375 Dave Kreskowiak 238 CPallini 220

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900