optimizing a query to use a join between two tables and translate rows to columns

Question

I know it's an already done question, but all the answer I found do not suits my needs and, more of this, I am unable to tail a proper solution by myself.

I explain the situation:

2 tables (user, user_preferences) in the first one there's, as you probably guessed, the name, last name, id and login (there's more data but theese are the ones I need) and in the second one we have user_id, preferences_key and preferences_value.

If I run my query:

select a.id, a.login, a.first_name, a.last_name, b.preferences_key from users a, user_preferences b where a.id=b.user_id and b.preferences_key like 'msg%';

I receive back an answer like this:

+----+---------+---------------+---------------+----------------------+
| id | login   | first_name    | last_name     | preferences_key      |
+----+---------+---------------+---------------+----------------------+
|  4 | usrn1   | User1         | NumberOne     | msg002               |
|  7 | usrn5   | User5         | NumberFive    | msg001               |
|  7 | usrn5   | User5         | NumberFive    | msg002               |
| 10 | usrn9   | User0         | NumberNine    | msg002               |
+----+---------+---------------+---------------+----------------------+

I'm trying to figure out how to switch from this view to this one:

+----+---------+---------------+---------------+--------+--------+
| id | login   | first_name    | last_name     | msg001 | msg002 |
+----+---------+---------------+---------------+--------+--------+
|  4 | usrn1   | User1         | NumberOne     | No     | Yes    |
|  7 | usrn5   | User5         | NumberFive    | Yes    | Yes    |
| 10 | usrn9   | User0         | NumberNine    | No     | Yes    |
+----+---------+---------------+---------------+--------+--------+

If you have any suggestion will be very appreciated, and, by the way, if you can add some more explanation I'll appreciate it even more.

Thank you

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Sam M, Aug 23 '18 at 15:59
how many possible value you have for preference_key? - only two as in your question or you have more ? — ScaisEdge, Aug 23 '18 at 16:04
Hi, there could be a lot. they are a code that identify a news, and obviosly they increase as new news come out. — Emiliano Baccini, Aug 24 '18 at 10:20

Gabe Gates · Answer 1 · 2018-08-23T20:00:56.053

There isn't really an easy way to pivot a table like you want easily that I know of.

There is the following manual approach by JOINing to the same table multiple times. Something like the following should work:

SELECT 
    a.id, a.login, a.first_name, a.last_name, 
    IF(b1.preferences_key IS NULL, 'No', 'Yes') msg001,
    IF(b2.preferences_key IS NULL, 'No', 'Yes') msg002
FROM
    users a
LEFT JOIN user_preferences b1
    ON b1.user_id = a.id
    AND b1.preferences_key = 'msg001'
LEFT JOIN user_preferences b2
    ON b2.user_id = a.id
    AND b2.preferences_key = 'msg002';

If this doesn't help. check out MySQL pivot table

optimizing a query to use a join between two tables and translate rows to columns

1 Answers1