0

i try to insert selected tranposed certain column table from one single table into another table with parameter based on employee id and the employee date. But here i was stuck with my query btw its worked for litle rows and column,but here my data table is almost much data there.Maybe there is 90 rows employee data. So here i want to change table format from old table into new format table with transposing it. btw it was still fail.meanwhile itry just using id and time,but also the time_out always shown the min() time also time_in column.

so here my query:

INSERT INTO procestable (id, time_in, time_out)
SELECT
ID_karyawan,
MIN(CASE WHEN row_number = 1 THEN jam END) as time_in,
MAX(CASE WHEN row_number = 2 THEN jam END) as time_out    
FROM (
SELECT
    ID_karyawan,
     jam,
    ROW_NUMBER() OVER (PARTITION BY ID_karyawan ORDER BY jam) 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

ecpected table:

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
2   Yuwono  06:58:41    17:24:41    2023-07-21  masuk   keluar
3   Ety 07:51:48    17:04:07    2023-07-21  masuk   keluar
4   Joseph  07:40:31    17:03:48    2023-07-21  masuk   keluar

ive tried from: [soource1][1] [source2][2] [1]: https://www.alibabacloud.com/help/en/maxcompute/use-cases/transpose-rows-to-columns-or-columns-to-rows [2]: https://stackoverflow.com/questions/67083311/transpose-multiple-rows-to-multiple-columns-with-no-aggregate-using-t-sql

request
  • 37
  • 7

0 Answers0