无需Pivot：将时间与信息列插入目标表的SQL查询问题

需要把table1中的时间列jam和状态列arah转换为宽表格式，插入到procestable中。尝试过Pivot不适用，用CASE函数和ROW_NUMBER子查询时始终出错，现寻求正确SQL写法。

原查询语句 #

INSERT INTO procestable (id,name, time_in, time_out,date,info1,info2)
SELECT
ID_karyawan,
nama_karyawan,
MAX(CASE WHEN row_number = 1 THEN jam END) as time_in,
MAX(CASE WHEN row_number = 2 THEN jam END) as time_out,
tanggal,
MAX(CASE WHEN row_number = 1 THEN jam END) as info1,
MAX(CASE WHEN row_number = 2 THEN jam END) as info2    
FROM (
SELECT
ID_karyawan,
nama_karyawan,
jam,
tanggal,
arah,
ROW_NUMBER() OVER (PARTITION BY ID_karyawan,nama_karyawan,jam,tanggal,arah ORDER BY jam,arah) 
as row_number
FROM table1
) AS table1
GROUP BY ID_karyawan;

table1原始数据 #

ID_karyawan  nama_karyawan  jam      tanggal    arah
1             ridho     07:44:45    2023-07-20  masuk
1             ridho     17:04:46    2023-07-20  keluar
3             Yuwono    17:24:47    2023-07-20  keluar
3             Yuwono    06:58:41    2023-07-20  masuk
4             Ety       07:51:48    2023-07-20  masuk
4             Ety       17:04:07    2023-07-20  keluar
5             Joseph    17:03:48    2023-07-20  keluar
5             Joseph    07:40:31    2023-07-20  masuk
1             ridho     07:44:45    2023-07-21  masuk
1             ridho     17:04:46    2023-07-21  keluar
3             Yuwono    17:24:47    2023-07-21  keluar
3             Yuwono    06:58:41    2023-07-21  masuk
4             Ety       07:51:48    2023-07-21  masuk
4             Ety       17:04:07    2023-07-21  keluar
5             Joseph    17:03:48    2023-07-21  keluar
5             Joseph    07:40:31    2023-07-21  masuk
1             ridho     07:44:45    2023-07-22  masuk
1             ridho     17:04:46    2023-07-22  keluar
3             Yuwono    17:24:47    2023-07-22  keluar
3             Yuwono    06:58:41    2023-07-22  masuk
4             Ety       07:51:48    2023-07-22  masuk
4             Ety       17:04:07    2023-07-22  keluar
5             Joseph    17:03:48    2023-07-22  keluar
5             Joseph    07:40:31    2023-07-22  masuk

期望插入后的procestable结果 #

id  name    time_in     time_out       date    info1   info2
1   ridho   07:44:45    17:04:46    2023-07-20  masuk   keluar
3   Yuwono  06:58:41    17:24:47    2023-07-20  masuk   keluar
4   Ety     07:51:48    17:04:07    2023-07-20  masuk   keluar
5   Joseph  07:40:31    17:03:48    2023-07-20  masuk   keluar
1   ridho   07:44:45    17:04:46    2023-07-21  masuk   keluar
3   Yuwono  06:58:41    17:24:47    2023-07-21  masuk   keluar
4   Ety     07:51:48    17:04:07    2023-07-21  masuk   keluar
5   Joseph  07:40:31    17:03:48    2023-07-21  masuk   keluar
1   ridho   07:44:45    17:04:46    2023-07-22  masuk   keluar
3   Yuwono  06:58:41    17:24:47    2023-07-22  masuk   keluar
4   Ety     07:51:48    17:04:07    2023-07-22  masuk   keluar
5   Joseph  07:40:31    17:03:48    2023-07-22  masuk   keluar

原查询的问题 #

1. ROW_NUMBER分区错误：PARTITION BY包含了jam和arah，导致每条单独的记录都被划分为独立分区，row_number始终为1，无法区分上下班记录。
2. GROUP BY不完整：仅按ID_karyawan分组，漏掉了nama_karyawan和tanggal，不符合SQL分组规则（非聚合列必须出现在GROUP BY中）。
3. 字段取值错误：info1和info2应该取arah字段的值，而非jam。

修正后的SQL写法 #

写法1：直接用CASE判断状态（更简洁） #

适用于每天只有一次上班、一次下班记录的场景：

INSERT INTO procestable (id, name, time_in, time_out, date, info1, info2)
SELECT
    ID_karyawan,
    nama_karyawan,
    MAX(CASE WHEN arah = 'masuk' THEN jam END) AS time_in,
    MAX(CASE WHEN arah = 'keluar' THEN jam END) AS time_out,
    tanggal AS date,
    'masuk' AS info1,
    'keluar' AS info2
FROM table1
GROUP BY ID_karyawan, nama_karyawan, tanggal;

写法2：用ROW_NUMBER编号（适用于多打卡记录场景） #

如果员工一天可能有多次打卡，需要取最早的上班、最晚的下班记录时使用：

INSERT INTO procestable (id, name, time_in, time_out, date, info1, info2)
SELECT
    ID_karyawan,
    nama_karyawan,
    MAX(CASE WHEN arah = 'masuk' AND rn = 1 THEN jam END) AS time_in,
    MAX(CASE WHEN arah = 'keluar' AND rn = 1 THEN jam END) AS time_out,
    tanggal AS date,
    'masuk' AS info1,
    'keluar' AS info2
FROM (
    SELECT
        ID_karyawan,
        nama_karyawan,
        jam,
        tanggal,
        arah,
        ROW_NUMBER() OVER (PARTITION BY ID_karyawan, tanggal, arah ORDER BY 
            CASE arah WHEN 'masuk' THEN jam ELSE NULL END,
            CASE arah WHEN 'keluar' THEN jam ELSE NULL END DESC
        ) AS rn
    FROM table1
) t
GROUP BY ID_karyawan, nama_karyawan, tanggal;

说明 #

• 两种写法均按员工+日期分组，确保每天的记录单独生成一行数据。
• 写法1逻辑简单直接，适合固定上下班打卡的场景；写法2更灵活，能处理多打卡记录的筛选需求。

内容的提问来源于stack exchange，提问作者request