MySQL - Pivot rows to comma separated values [duplicate]

Question

I have 3 tables.

1. user
2. user_role
3. user_role_mapping

Sample record,

user

+--------+----------+---------------+
| userid | username | email         |
+--------+----------+---------------+
|      1 | user1    |[email protected] |
|      2 | user2    |[email protected] |
+--------+----------+---------------+

user_role

+--------+----------+
| roleid | rolename |
+--------+----------+
|      1 | user     |
|      2 | manager  |
|      3 | director |
|      4 | admin    |
+--------+----------+

User_role_mapping

+--------+------+
| roleid |userid|
+--------+------+
|      1 | 1    |
|      2 | 1    |
|      3 | 1    |
+--------+------+

Query

select u.userid, u.username, u.email,
  count(case when ur.rolename = 'user' THEN 1 END) user,
  count(case when ur.rolename = 'manager' THEN 1 END) manager,
  count(case when ur.rolename = 'director' THEN 1 END) director,
  count(case when ur.rolename = 'admin' THEN 1 END) admin
from user_role ur
left join userrole_mapping urm
  on ur.roleid = urm.roleid
left join user u
  on urm.userid = u.userid
group by u.userid, u.username, u.email

Result:-

+--------+----------+---------------+------|---------|----------|-------|
| userid | username | email         | user | manager | director | admin |
+--------+----------+---------------+------------------------------------
|      1 | user1    |[email protected] | 1    |   1     |  1       |  1    |
+--------+----------+---------------+------------------------------------

Here, I am hardcoding the role in the query and the new role can get added in the future and I do not want to do the code change. Is there any better approach? I am fine with comma separated result for role in the result.

count(case when ur.rolename = 'role' THEN 1 END) can be simplified to SUM(ur.rolename = 'role'). — Akina
– Akina, Commented Feb 15, 2021 at 6:34

user1578872 · Accepted Answer · 2021-02-15 15:47:56Z

1

For comma separation you can use group_concat:

select u.user_id, u.user_name, u.email,
       Group_concat(ur.role_name) roles
from user u
left join user_role_mapping urm
  on urm.user_id = u.user_id
left join user_role ur
  on ur.role_id = urm.role_id
where u.user_id = ?
group by u.user_id;

Another, maybe better approach w/o hardcoding would be to query the roles separately:

select u.user_id, u.user_name, u.email, ur.role_name
from user u
left join user_role_mapping urm
  on urm.user_id = u.user_id
left join user_role ur
  on ur.role_id = urm.role_id
where u.user_id = ?;

edited Feb 15, 2021 at 15:47

user1578872

9,26837 gold badges133 silver badges239 bronze badges

answered Feb 15, 2021 at 6:33

MrTux

34.4k30 gold badges120 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - Pivot rows to comma separated values [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related