sql-server - SQL Server Pivot 考勤记录根据 Time-In 和 Time-Out

Question

有人知道我怎样才能提出我的要求的解决方案吗？我有结构（[ID]，[DATE]，[EMPID]，[TIME]）的考勤表。见下文：

ID  DATE        EMPID   TIME        REMARKS
1   20/09/2018  9001    7:30        This will be the In_1 as it is the first TIME-IN in for EMPID=9001
2   20/09/2018  9001    7:40        This will be the In_2 as it is the second TIME-IN for EMPID=9001
3   20/09/2018  9001    7:50        Not included
4   20/09/2018  9001    17:10       This will be the Out_1 as it is the first TIME-OUT for EMPID=9001
5   20/09/2018  9001    17:50       This will be the Out_2 as it is the second TIME-OUT for EMPID=9001
6   20/09/2018  9001    18:00       Not included
7   20/09/2018  9002    7:20        This will be the In_1 as it is the first TIME-IN in for EMPID=9002
8   20/09/2018  9002    7:21        This will be the In_2 as it is the second TIME-IN for EMPID=9002
9   20/09/2018  9002    18:00       This will be the Out_1 as it is the first TIME-OUT for EMPID=9002
10  20/09/2018  9003    7:00        This will be the In_1 as it is the first TIME-IN in for EMPID=9003
11  20/09/2018  9003    17:10       This will be the Out_1 as it is the first TIME-OUT for EMPID=9003
11  20/09/2018  9003    17:12       This will be the Out_2 as it is the second TIME-OUT for EMPID=9003
11  20/09/2018  9003    17:15       Not included

输出将与以下相同：

DATE        EMPID   IN_1    IN_2    OUT_1   OUT_2
20/09/2018  9001    7:30    7:40    17:10   17:50
20/09/2018  9002    7:20    7:21    18:00   
20/09/2018  9003    7:20    17:10   17:12

标签： sql-servertsqlpivot

Answer 1

由于您没有可以从 OUT 中识别 IN 的列，因此我可以任意使用 12:00 作为分隔符时间。

; WITH
cte AS
(
    SELECT  *, TYPE = CASE WHEN TIME < '12:00' THEN 'IN' ELSE 'OUT' END
    FROM    your_table
),
cte2 AS
(
    SELECT  *, rn = row_number() over (partition by EMPID, DATE, TYPE ORDER BY TIME)
    FROM    cte
)
SELECT DATE, EMPID,
       IN_1  = MAX(CASE WHEN TYPE = 'IN'  AND rn = 1 THEN TIME END),
       IN_2  = MAX(CASE WHEN TYPE = 'IN'  AND rn = 2 THEN TIME END),
       OUT_1 = MAX(CASE WHEN TYPE = 'OUT' AND rn = 1 THEN TIME END),
       OUT_2 = MAX(CASE WHEN TYPE = 'OUT' AND rn = 2 THEN TIME END)
FROM    cte2
GROUP BY DATE, EMPID