SQLite 3.27

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

CREATE TABLE IF NOT EXISTS tablename (id text, username text, score integer, UNIQUE(id,username))
INSERT INTO tablename VALUES
  ('1', 'User1', '5'),
  ('1', 'User2', '7'),
  ('1', 'User3', '10'),
  ('2', 'User1', '6'),
  ('2', 'User2', '2');
SELECT id,
       MAX(CASE WHEN username = 'User1' THEN score END) ScoreUser1,
       MAX(CASE WHEN username = 'User2' THEN score END) ScoreUser2
FROM tablename
WHERE username IN ('User1', 'User2')
GROUP BY id;


id
ScoreUser1
ScoreUser2


1
5
7

2
6
2