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 ?
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
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;
