I'm looking to query a bunch of user and usermeta data into a single table. I'm able to do so by creating multiple temporary tables and a pivot:
create temporary table meta
select u.ID, u.user_login, meta_key, meta_value from wp_users u
inner join wp_usermeta m on m.user_id = u.ID;
create temporary table users
select ID, user_login,
MAX(CASE WHEN meta_key = 'first_name' THEN meta_value END) as first_name,
MAX(CASE WHEN meta_key = 'middle_name' THEN meta_value END) as middle_name,
MAX(CASE WHEN meta_key = 'last_name' THEN meta_value END) as last_name,
MAX(CASE WHEN meta_key = 'university_program' THEN meta_value END) as university_program,
MAX(CASE WHEN meta_key = 'wp_capabilities' THEN meta_value END) as wp_capabilities
from meta
group by ID, user_login;
drop temporary table meta;
select * from users
where wp_capabilities like '%um_student%';
drop temporary table users;
However, is there a better and shorter way to run this query without the need to create temporary tables or the pivot?