How can I write a MySQL query to have this result?

Question

sorry for the generic title but I don't know how to explain this in a few words. I have a user_role table, it's part of a classical structure to manage a realm in tomcat with a database/jdbc. It's like this:

userid | roleid
user1  | role1
user1  | role2
user2  | role2
user2  | role3

How can I write a query in mysql (if it's possible) to have a result like this:

         | role1 | role2 | role3 |   
  user1  |   x   |   x   |       |   
  user2  |       |   x   |   x   |

Thanks in advance
Cheers
Matteo

look on http://stackoverflow.com/questions/7674786/mysql-pivot-table — Haim Evgi, Feb 13 '17 at 10:08

sumit · Answer 1 · 2017-02-13T10:47:32.667

0

Mysql do not support pivoting out of the box, you can use CASE WHEN

select SUM(role1),sum(role2),sum(role3),user FROM (
select 
case when role = 'role1' then 1   else 0 end as role1,
case when role = 'role2' then 1   else 0 end as role2,
case when role = 'role3' then 1   else 0 end as role3,
user
FROM tbl
)tmp1
 group by user

edited Feb 13 '17 at 10:47

answered Feb 13 '17 at 10:30

sumit

15,003
12
69
110

Poor sample by using fixed values... You should have implied they actually had a table name even though not explicitly provided – DRapp Feb 13 '17 at 10:39

score 0 · Answer 2 · answered Feb 13 '17 at 10:38

Simplified from Sumit's offer

select 
      ur.userid,
      max( case when ur.roleid = 'role1' then 'X' else ' ' end ) as role1,
      max( case when ur.roleid = 'role2' then 'X' else ' ' end )  as role2,
      max( case when ur.roleid = 'role3' then 'X' else ' ' end ) as role3
   from
      UserRoles ur
   group by
      ur.userid

Now, if you want the user's actual name and have a corresponding USER table, join to that to get the name.

select 
      u.userName,
      max( case when ur.roleid = 'role1' then 'X' else ' ' end ) as role1,
      max( case when ur.roleid = 'role2' then 'X' else ' ' end )  as role2,
      max( case when ur.roleid = 'role3' then 'X' else ' ' end ) as role3
   from
      UserRoles ur
         JOIN Users u
            on ur.userid = u.userid
   group by
      ur.userid

How can I write a MySQL query to have this result?

2 Answers2