Forum Discussion
Lexterb
Mar 07, 2024Copper Contributor
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
- olafhelperBronze Contributor
Lexterb , the better term for "Rows to columns" is pivot data, see Using PIVOT and UNPIVOT - SQL Server | Microsoft Learn