sqlite - 在 SQLite 中将行转置为列

Question

我有这样的数据:

我正在尝试将其转换为这个(使用 SQLite)。在所需的结果中，在每个 id 内，每个开始都应与按时间顺序最接近的结束位于同一行。如果一个id有开始但没有结束(比如id=4)，那么对应的结束，将为空(如下所示)。

我已经尝试过了

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

但结果是这样的，这是错误的，因为 id=5 只有一行，而它应该有两行:

  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

非常感谢任何帮助

    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

• 左连接(保留 ID“4”的仅开始行)两个动态表、开始日期和结束日期。
• 使用 min() 和 group by 获取略高于开始日期的最小结束日期(相同 ID)。
• 按 ID 排序，然后开始日期。

我在与您的转储类似的测试表上对此进行了测试，但没有“NOT NULL”也没有“PRIMARY KEY”。我想对于这个测试表来说这是无关紧要的；否则请解释一下效果。

注意:
在内部找到 id 5 的三对日期(那些匹配 end>start 的日期)，但对于两种不同 ID 组合中的每一种，仅使用最低端 (min(end)) 转发这些日期并开始按 ID 分组，开始。因此，不会返回 end>start 但 end 不是最小值的行。这使得两条线具有所需的开始/结束对。

输出(.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  

更新:纳入@MatBailie 的有用评论。