MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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');

Records: 5  Duplicates: 0  Warnings: 0


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