I need to query some data from a MySQL key value table but I want get a "normal" table as result with specified keys as columns.
Example of my table USERS:
ID, user_id, key, value
----------------------------------
1 1 first_name Peter
2 1 last_name Sputnick
3 2 first_name Jan
4 2 last_name Putgraver
5 2 country Netherlands
I want this as query result:
ID, first_name, last_name
----------------------------
1 Peter Sputnick
2 Jan Putgraaf
Is there a good and efficient way to achieve this? Note that I don't want to include the country column in my result.
I need this because i need to join this data with data from another table and i don't want to use a sub query for each field i need. So I don't want to do something like this:
SELECT *,
(SELECT value
FROM users
WHERE user_id = o.user_id
AND key = first_name),
(SELECT value
FROM users
WHERE user_id = o.user_id
AND key = last_name),
FROM orders o
You can use conditional aggregation:
select
id,
max(case when key = 'fist_name' then value end) first_name,
max(case when key = 'last_name' then value end) last_name
from users
group by id
for thsi you must use conditional aggregation,
CREATE TABLE USERS ( `ID` VARCHAR(6), `user_id` VARCHAR(9), `key` VARCHAR(10), `value` VARCHAR(11) );
INSERT INTO USERS (`ID`, `user_id`, `key`, `value`) VALUES ('1', '1', 'first_name', 'Peter'), ('2' , '1', 'last_name', 'Sputnick'), ('3', '2', 'first_name', 'Jan'), ('4', '2', 'last_name', 'Putgraver'), ('5', '2', 'country', 'Netherlands');
SELECT user_id , MAX(IF(`key` ='first_name',`value`,NULL )) fisrt_name , MAX(IF(`key` ='last_name',`value`,NULL )) last_name FROM USERS GROUP BY user_id;user_id | fisrt_name | last_name :------ | :--------- | :-------- 1 | Peter | Sputnick 2 | Jan | Putgraver
db<>fiddle here
If you want all keys you must use a pivot table