How to convert rows into columns in SQL Server?

问题:

I have this table from fingerprint sensor and I need to show the result in one row

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

Result needed like this:

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

Can anyone help me ?

回答1:

Simple aggregation should do:

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;

Or If you want, you can use pivot:

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

回答2:

Alternatively you could use PIVOT, which has been developed specifically to turn ROWS into COLUMNS.

SELECT id
     , [I] as [IN-DateTime]
     , [O] as [OUT-DateTime]
FROM Table t
PIVOT (max(dateTime) for flag in ([I], [O])) as pvt;