title: Single columns from several rows into several columns in one record, but allow NULL in the rows
body: <p>I'm trying to combine single rows from multiple records into several columns in one record.</p>
  <p>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.</p>
  <p>This is a simplified example, in my actual production database, it's scaled up to several of these 'numbers'.</p>
  <p>From this example, person 3 hasn't chosen their 2nd number yet.</p>
  <p>I tried this query:</p>
  <pre class="lang-sql prettyprint-override"><code>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'
  </code></pre>
  <p>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.</p>
  <p>Here is a dump of the sample database where I'm testing this.</p>
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  <p>I'd also appreciate being told how I could scale this up to say, 3 numbers, or 4 numbers, or even more than that.</p>