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

user_19563340 0
Viewed 35

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
max case where-clause group-by sqlite
1 Answers

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.

user_104988280
Related