Select with multiple column creation results

Question

I have 2 tables [user] and [userrights]

[user]

id      name
1       test1
2       test2

[usergroups]

id      name
1       member
1       admin
1       18
2       new
2       16

And I wanted to have this results

id      name        group1      group2      group3
1       test1       member      admin       18
2       test2       new         18          null

I tried this but I think it is the wrong way

SELECT *,IF(rightname = 'Member', 'Member', NULL) as status
FROM user,userrights 
where user.iduser = userrights.iduser

with this I get these results

id      name        rights      member
1       test1       member      member
1       test1       admin       null
1       test1       18          null

i also tried this

     SELECT user.iduser,GROUP_CONCAT(rightname) as groups FROM user,userrights where user.iduser = userrights.iduser GROUP BY iduser;

so i get a much better result

id      groups
1       member,admin,18
2       new,16

but i wanted to have for each group a new column

Can a user only be part of 3 groups maximum? Or you could join the two tables on the id and use that data — Alagu Veerappan, Mar 27 '19 at 12:36
Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Nick, Mar 27 '19 at 12:37
the ammount of the groups are not limmited, i think i found something usefull for me. with group_concat i just have only one column but its ok for the moment. pls tell me if someone have a better idea — Kim, Mar 27 '19 at 12:48

score 0 · Accepted Answer · answered Mar 27 '19 at 12:58

0

If you really want separate columns, the simplest approach might be parsing the group_concat():

select u.id, u.name,
       substring_index(group_concat(ur.rightname order by ur.rightname), ',', 1) as right1,
       substring_index( substring_index(group_concat(ur.rightname order by ur.rightname), ',', 2), ',', -1) as right2,
       substring_index( substring_index(group_concat(ur.rightname order by ur.rightname), ',', 3), ',', -1) as right3
from user u join
     userrights ur
     on u.iduser = ur.iduser 
group by u.id, u.name;

Notice that I also fixed your archaic join syntax and added table aliases.

answered Mar 27 '19 at 12:58

Gordon Linoff

1,242,037
58
646
786

thanks for your answer, so when i have 15 groups i need to make 15 substrings? – Kim Mar 27 '19 at 13:11
@Kim . . . That is one solution. Usually, concatenating them using `group_concat()` is sufficient. – Gordon Linoff Mar 27 '19 at 13:12

Select with multiple column creation results

1 Answers1