How do I "Rotate" a set of results from rows into columns for users in various groups

Question

I'm running MySQL 5.7.

I'm looking for a SQL query that will "rotate" some data from rows into columns. The tables are like so:

users table

user_id,first_name
1,Alice
2,Bob
3,Eve
4,Mallory

user_groups table

user_id,group_name
1,Administrator
2,Editor
2,Contributor
3,Viewer

There are a finite, small set of groups, and I want to produce a result like:

user_id,first_name,Administrator,Editor,Contributor,Viewer
1,Alice,Yes,No,No,No
2,Bob,No,Yes,Yes,No
3,Eve,No,No,No,Yes
4,Mallory,No,No,No,No

I'm not even sure what I would call that kind of query, but that's what I'd like to do. "Yes" and "No" can be 0 and 1, or NULL and 1, that's all fine.

Any ideas?

Answer 1

SELECT u.user_id,
 MAX(CASE group_name WHEN 'Administrator' THEN 'Yes' ELSE 'No' END) AS Administrator,
 MAX(CASE group_name WHEN 'Editor' THEN 'Yes' ELSE 'No' END) AS Editor,
 MAX(CASE group_name WHEN 'Contributor' THEN 'Yes' ELSE 'No' END) AS Contributor,
 MAX(CASE group_name WHEN 'Viewer' THEN 'Yes' ELSE 'No' END) AS Viewer
FROM users AS u
LEFT OUTER JOIN user_groups AS g USING (user_id)
GROUP BY u.user_id;

MySQL does not have support for any special syntax for PIVOT or CROSSTAB as some other brands of SQL database have.