sql - SQL考勤查询

Question

我想对上一个问题做一个类似的查询：SQL Query for time In/Out出席

我的数据格式如下：

    --------------------------------
    ID_Emp| Name    |   Date
    --------------------------------
    11    |Jon     |14/05/2014 8:00
    11    |Jon     |14/05/2014 12:00
    11    |Jon     |14/05/2014 12:30
    11    |Jon     |14/05/2014 16:00
    12    |Beth    |14/05/2014 8:00
    12    |Beth    |14/05/2014 12:00
    12    |Beth    |14/05/2014 12:30
    12    |Beth    |14/05/2014 16:00
    13    |Bob     |14/05/2014 8:00
    13    |Bob     |14/05/2014 12:00
    13    |Bob     |14/05/2014 12:30
    13    |Bob     |14/05/2014 16:00

但想用以下格式构建查询：

    -----------------------------

    ID_Emp|Name    |Date            |TimeIn1 |TimeOut1 |TimeIn2 |TimeOut2
    ----------------------------------------------------------------------
    11    |Jon     |14/05/2014      |8:00    |12:00    |12:30   |16:00
    12    |Beth    |14/05/2014      |8:00    |12:00    |12:30   |16:00
    13    |Bob     |14/05/2014      |8:00    |12:00    |12:30   |16:00

标签： sqlsql-server-2008-r2

Answer 1

您可以使用row_number()条件聚合：

select id_emp, name, convert(date, date),
       convert(time, max(case when seqnum = 1 then date end)) as timein1,
       convert(time, max(case when seqnum = 2 then date end)) as timein2,
       convert(time, max(case when seqnum = 3 then date end)) as timein3,
       convert(time, max(case when seqnum = 4 then date end)) as timein4
from (select t.*,
             row_number() over (partition by id_emp, convert(date, date) order by date) as seqnum
      from t
     ) t
group by id_emp, name, convert(date, date);