Forum Discussion

Lexterb's avatar
Lexterb
Copper Contributor
Mar 07, 2024

Rows to columns

This is my source table.

I use this script but the output is not aligned, iI want that the same date is aligned.

SELECT top 10 empno
, [0] as [punch_code0]
, [1] as [punch_code1]
, [2] as [punch_code2]
, [3] as [punch_code3]
, [4] as [punch_code4]
, [5] as [punch_code5]
FROM biometric_log t
PIVOT (max(punch_date) for punch_code in ([0],[1],[2],[3],[4],[5] )) as pvt

I want the output should look like this

empno        punch_code0              punch_code1 punch_code2  punch_code3
0000000001 2024-02-01 07:58:00 NULL              NULL               2024-02-01 17:06:00
0000000001 2024-02-02 07:53:00 NULL              NULL               2024-02-02 17:05:00
0000000001 2024-02-03 07:49:00 NULL              NULL               2024-02-03 16:04:00
0000000001 2024-02-05 07:52:00 NULL              NULL               2024-02-05 17:05:00
0000000001 2024-02-06 07:53:00 NULL              NULL               2024-02-06 17:05:00
 

Resources