sql server数据透视表查询

Question

我们正在尝试将出勤日志数据转换为数据透视表。为了简单起见，实际数据的形式为:

EmployeeId, InOrOut,  DateTime
1            0        2019-01-01 08:00:00
1            1        2019-01-01 17:00:00
1            0        2019-01-02 08:00:00
1            1        2019-01-02 17:00:00
2            0        2019-01-01 08:00:00
2            1        2019-01-01 17:00:00

我们需要像这样:

EmployeeId, Date,        InTime , OutTime
1           2019-01-01   08:00    17:00
1           2019-01-02   08:00    17:00
2           2019-01-01   08:00    17:00

但是我们所做的查询似乎并不像这样工作。查询如下:

SELECT * FROM
(
SELECT aml.EnrollNumber, aml.A_Date, aml.InOutMode, aml.A_Time
 FROM dbo.Attendence_Machines_LOG aml) AS AttendanceTable
PIVOT (
     max(A_Date)
    FOR InOutMode in ([1],[0])
) as PivotTable

关于透视有很多困惑，个人找不到太多教程。

我们如何告诉查询将数据放入新行的依据是什么(例如在本例中，我们如何告诉查询根据日期和员工 ID 分隔记录)

感谢任何帮助

Answer 1

您可以进行聚合:

SELECT aml.EnrollNumber, aml.A_Date,
       MAX(CASE WHEN aml.InOutMode = 1 THEN aml.A_Time END),
       MAX(CASE WHEN aml.InOutMode = 0 THEN aml.A_Time END)
FROM dbo.Attendence_Machines_LOG AS aml
GROUP BY aml.EnrollNumber, aml.A_Date;

编辑:问题编辑后:

SELECT aml.EnrollNumber, CAST(aml.A_Date AS DATE),
       MAX(CASE WHEN aml.InOutMode = 1 THEN aml.A_Time END),
       MAX(CASE WHEN aml.InOutMode = 0 THEN aml.A_Time END)
FROM dbo.Attendence_Machines_LOG AS aml
GROUP BY aml.EnrollNumber, CAST(aml.A_Date AS DATE);