Single columns from several rows into several columns in one record, but allow NULL in the rows

Question

I'm trying to combine single rows from multiple records into several columns in one record.

Say we have a database of people and they've all chosen 2 numbers. But, some people have only chosen 1 number and haven't submitted their 2nd number.

This is a simplified example, in my actual production database, it's scaled up to several of these 'numbers'.

From this example, person 3 hasn't chosen their 2nd number yet.

I tried this query:

SELECT ppl.*,
       cn1.chosen_num AS first_num,
       cn2.chosen_num AS second_num

  FROM people AS ppl
       LEFT JOIN
       chosenNumbers AS cn1
       LEFT JOIN
       chosenNumbers AS cn2

 WHERE ppl.numid = cn1.personid
   AND ppl.numid = cn2.personid
   
   AND cn1.type = 'first'
   AND cn2.type = 'second'

But it doesn't return any information on Person3, since they haven't chosen their second number yet. However, I want data on EVERYONE involved in this number guessing, but I want to be able to see the first and second numbers they've guessed.

Here is a dump of the sample database where I'm testing this.

BEGIN TRANSACTION;
CREATE TABLE people (numid INTEGER PRIMARY KEY, name TEXT);
INSERT INTO people VALUES(1,'Person1');
INSERT INTO people VALUES(2,'Person2');
INSERT INTO people VALUES(3,'Person3');
CREATE TABLE chosenNumbers (numid INTEGER PRIMARY KEY, chosen_num INTEGER, type TEXT, personid INTEGER, FOREIGN KEY(personid) REFERENCES people(numid));
INSERT INTO chosenNumbers VALUES(1,101,'first',1);
INSERT INTO chosenNumbers VALUES(2,102,'second',1);
INSERT INTO chosenNumbers VALUES(3,201,'first',2);
INSERT INTO chosenNumbers VALUES(4,202,'second',2);

-- Person 3 hasn't chosen their 2nd number yet..
-- But I want data on them, and the query above
-- doesn't work.
INSERT INTO chosenNumbers VALUES(5,301,'first',3);
COMMIT;

I'd also appreciate being told how I could scale this up to say, 3 numbers, or 4 numbers, or even more than that.

Answer 1

You can use conditional aggregation:

SELECT p.*,
       MAX(CASE WHEN c.type = 'first' THEN c.chosen_num END) AS first_num,
       MAX(CASE WHEN c.type = 'second' THEN c.chosen_num END) AS second_num
FROM people AS p LEFT JOIN chosenNumbers AS c
ON p.numid = c.personid
GROUP BY p.numid;

You can expand the code for more columns.

See the demo.