将行转换为允许重复的列

Question

考虑下面的表和行：

清单 A。

ID, name, event, type
1, 'John Doe', '2010-09-01 15:00:00.000', 'input'
1, 'John Doe', '2010-09-03 11:00:00.000', 'input'
1, 'John Doe', '2010-09-04 17:00:00.000', 'input'
1, 'John Doe', '2010-09-02 15:00:00.000', 'output'
1, 'John Doe', '2010-09-03 16:00:00.000', 'output'
1, 'John Doe', '2010-09-06 17:00:00.000', 'output'

我想要的是将行转换为列，这样我就可以有两个不同的列：输入事件和输出事件。像：

清单 B.

ID, name, input event, output event
1, 'John Doe', '2010-09-01 15:00:00.000', '2010-09-02 15:00:00.000'
1, 'John Doe', '2010-09-03 11:00:00.000', '2010-09-03 16:00:00.000'
1, 'John Doe', '2010-09-04 17:00:00.000', '2010-09-06 17:00:00.000'

我能够得到类似以下的内容：

清单 C.

ID, name, input event, output event
1, 'John Doe', '2010-09-01 15:00:00.000', null
1, 'John Doe', '2010-09-03 11:00:00.000', null
1, 'John Doe', '2010-09-04 17:00:00.000', null
1, 'John Doe', null, '2010-09-02 15:00:00.000'
1, 'John Doe', null, '2010-09-03 16:00:00.000'
1, 'John Doe', null, '2010-09-06 17:00:00.000'

，但问题是如何展平行，因为重复的元组 ID-name 是相关的。要将行转换为列，我通常编写如下代码：

select ID, name, max(case when type = 'input' then event else null end) as 'input event', max(case when type = 'output' then event else null end) as 'output event' from events group by ID, name

，但是当然，GROUP BY 会忽略重复项，这就是我不想要的。

有什么想法如何通过查询来实现这一点吗？

如果有一个可移植的 sql 解决方案或 postgresql 就太好了，但任何想法都值得赞赏。

编辑：抱歉回复晚了。 AlexRednic 和 Mark Ba​​nnister 的两个解决方案都实现了我想要的。我最终选择了第二个，因为它对我来说看起来更清晰。谢谢大家的回答！

Answer 1

我从头开始写：

create table #a(
ID int,
name varchar(30),
event datetime,
type varchar(10)
)

insert #a
select  
1, 'John Doe', '2010-09-01 15:00:00.000', 'input'
union select 1, 'John Doe', '2010-09-01 16:00:00.000', 'input'
union select 1, 'John Doe', '2010-09-01 17:00:00.000', 'input'
union select 1, 'John Doe', '2010-09-02 15:00:00.000', 'output'
union select 1, 'John Doe', '2010-09-02 16:00:00.000', 'output'
union select 1, 'John Doe', '2010-09-02 17:00:00.000', 'output'

--这是解决方案sql

select 
    ID, 
    name, 
    case when type = 'input' then event else null end "input event",
    case when type = 'output' then event else null end "output event"
 from #a

Answer 2

select t1.id,t1.name,t1.event,t2.event from test t1
    inner join test t2 on t1.event <= t2.event 
        and t1.type = 'input' 
        and t2.type = 'output'
                   and t1.id = t2.id
                   and t1.name = t2.name

问题是您需要以某种方式链接输入/输出会话。在此查询中，我通过使用时间戳事件列来完成此操作。如果这不是您想要的，您能否提供更多信息？

更新：现在，您可以做一些后处理

with a as
(
select t1.id,t1.name,t1.event as in_event,t2.event as out_event from test t1
    inner join test t2 on t1.event <= t2.event 
                   and t1.type = 'input' 
                   and t2.type = 'output'
                   and t1.id = t2.id
                   and t1.name = t2.name
)
select id,name,in_event,min(out_event)
       from a
group by id,name,in_event

Answer 3

请尝试以下操作：

select ID, name, event as 'input event', 
       (select min(o.event) 
        from events o 
        where o.type = 'output' and 
              i.ID = o.ID and 
              i.name = o.name and 
              i.event < o.event) as 'output event' 
from events i
where i.type = 'input'
group by ID, name, event