Is it possible to use keys as columns and values as values for a key/value database structure?

Question

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

Moving values to column names is "pivoting". Clearly this is going to be a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 22 '20 at 12:11
@Strawberry Please accept my sincere Happyhammer Clappyhands ™. — philipxy, Jun 22 '20 at 12:37
@Strawberry Or is that Hammerhappy Handyclaps ™? (If only I there were a Clickicon™ I wouldn't have to spell it.) — philipxy, Jun 22 '20 at 12:40

score 2 · Answer 1 · answered Jun 22 '20 at 12:02

2

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

answered Jun 22 '20 at 12:02

GMB

216,147
25
84
135

nbk · Accepted Answer · 2020-06-22T12:22:47.820

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

Is it possible to use keys as columns and values as values for a key/value database structure?

2 Answers2