How do I select and join from a single table with 2 unique IDs based on one ID and concatenate the o-CodePudding

I don't have much experience with SQL so forgive me if it is a simple answer...

I am working on a personal project in Python and I am using sqlite3. I currently have a table created like this:

CREATE TABLE IF NOT EXISTS table (id text, username text, score integer, UNIQUE(id,username))

where the idea is two users can score the same ID differently, and also the same user can score multiple ID's.

ID	Username	Score
1	User1	5
1	User2	7
1	User3	10
2	User1	6
2	User2	2

How to I select to find common ID's among users as well as their scores? Is there a select command I can string up to return something like this? (i.e. common between users User1 and User2)

ID	Score User1	Score User2
1	5	7
2	6	2

CodePudding user response：

Use conditional aggregation:

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
HAVING COUNT(*) = 2;

See the demo.