sql-server - 如何在 SQL Server 中将行转换为列？

Question

我有这个来自指纹传感器的表格，我需要在一行中显示结果

ID |  DateTime                | Flag
----------------------------------------
41 |  2017-02-22 08:05:56.000 | I
41 |  2017-02-22 18:11:03.000 | O

需要这样的结果:

ID |  IN-DateTime             |  OUT-DateTime           
--------------------------------------------------------
41 |  2017-02-22 08:05:56.000 | 2017-02-22 18:11:03.000 

谁能帮帮我？

Answer 1

简单的聚合应该这样做:

select id,
    max(case when flag = 'I' then datetime end) indatetime,
    max(case when flag = 'O' then datetime end) outdatetime
from t
group by id;

或者如果你愿意，你可以使用pivot:

select id, [I] indatetime, [O] outdatetime
from t pivot (
    max(datetime) for flag in ([I],[O])
) as p