Transpose rows to columns in SQLite

Question

I have data like this:

I am trying to transform it to this (using SQLite). In the desired result, within each id, each start should be on the same row as the chronologically closest end. If an id has a start but no end (like id=4), then the corresponding end, will be empty (as shown below).

I have tried this

select 
    id,
    max( case when start_end = "start" then date end) as start,
    max(case when start_end = "end"   then date end ) as end
from df
group by id

But the result is this, which is wrong because id=5 only have one row, when it should have two:

  id      start        end
1  2 1994-05-01 1996-11-04
2  4 1979-07-18       <NA>
3  5 2010-10-01 2012-10-06

Any help is much appreciated

    CREATE TABLE mytable(
   id        INTEGER  NOT NULL PRIMARY KEY 
  ,start_end VARCHAR(5) NOT NULL
  ,date      DATE  NOT NULL
);
INSERT INTO mytable(id,start_end,date) VALUES (2,'start','1994-05-01');
INSERT INTO mytable(id,start_end,date) VALUES (2,'end','1996-11-04');
INSERT INTO mytable(id,start_end,date) VALUES (4,'start','1979-07-18');
INSERT INTO mytable(id,start_end,date) VALUES (5,'start','2005-02-01');
INSERT INTO mytable(id,start_end,date) VALUES (5,'end','2009-09-17');
INSERT INTO mytable(id,start_end,date) VALUES (5,'start','2010-10-01');
INSERT INTO mytable(id,start_end,date) VALUES (5,'end','2012-10-06');

Answer 1

select
  s.id        as id,
  s.date      as 'start',
  min(e.date) as 'end' -- earliest end date from "same id&start"
from
  -- only start dates
  (select id, date
   from intable
   where start_end='start'
   ) as s
left join -- keep the start-only lines
  -- only end dates
  (select id, date
   from intable
   where start_end='end'
   ) as e
on      s.id = e.id
  and s.date < e.date -- not too early
group by s.id, s.date -- "same id&start"
order by s.id, s.date; -- ensure sequence

• Left join (to keep the start-only line for id "4") two on-the-fly tables, start dates and end dates.
• Take the minimal end date which is just higher than start date (same id, using min()and group by.
• Order by id, then start date.

I tested this on a test table which is similar to your dump, but has no "NOT NULL" and no "PRIMARY KEY". I guess for this test table that is irrelevant; otherwise explain the effect, please.

Note:
Internally three pairs of dates for id 5 (those that match end>start) are found, but only those are forwarded with the lowest end (min(end)) for each of the two different combinations of ID and start group by ID, start. The line where end>start but end not being the minimum is therefor not returned. That makes two lines with start/end pairs as desired.

Output (with .headers on):

id|start|end
2|1994-05-01|1996-11-04  
4|1979-07-18|  
5|2005-02-01|2009-09-17  
5|2010-10-01|2012-10-06  

UPDATE: Incorporate helpful comments by @MatBailie.