I have the following data in a Table.
And i need output in below format.
I tried pivoting but seems like not able to resolve it. Can someone please guide me here?
Thanks in advance.
Edit: Data in Text Format.
| EmployeeID | ShiftCode | AttendanceDate | inDateTime | outDateTime | OverTimeHours |
|---|---|---|---|---|---|
| 26 | ShiftCC1 | 01-03-2022 | 01-03-2022 09:10 | 01-03-2022 18:10 | 1 |
| 26 | ShiftCC1 | 02-03-2022 | 02-03-2022 09:15 | 02-03-2022 18:15 | 2 |
| 26 | ShiftCC1 | 03-03-2022 | 03-03-2022 09:05 | 03-03-2022 18:05 | 2 |
| 26 | ShiftCC1 | 04-03-2022 | 04-03-2022 09:10 | 04-03-2022 18:10 | 1 |
| 26 | ShiftCC1 | 05-03-2022 | 05-03-2022 09:13 | 05-03-2022 18:13 | 2 |
| 26 | ShiftCC1 | 06-03-2022 | 06-03-2022 09:14 | 06-03-2022 18:14 | 3 |
| 26 | ShiftCC1 | 07-03-2022 | 07-03-2022 09:16 | 07-03-2022 18:16 | 2 |
| 26 | ShiftCC1 | 08-03-2022 | 08-03-2022 09:30 | 08-03-2022 18:30 | 1 |
| 26 | ShiftCC1 | 09-03-2022 | 09-03-2022 09:20 | 09-03-2022 18:20 | 2 |
| 26 | ShiftCC1 | 10-03-2022 | 10-03-2022 09:25 | 10-03-2022 18:25 | 3 |
output in Text Format:
| EmployeeID | ShiftCode | DataType | 01-03-2022 | 02-03-2022 | 03-03-2022 | 04-03-2022 | 05-03-2022 | 06-03-2022 | 07-03-2022 | 08-03-2022 | 09-03-2022 | 10-03-2022 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 26 | ShiftCC1 | InDateTime | 01-03-2022 09:10 | 02-03-2022 09:15 | 03-03-2022 09:05 | 04-03-2022 09:10 | 05-03-2022 09:13 | 06-03-2022 09:14 | 07-03-2022 09:16 | 08-03-2022 09:30 | 09-03-2022 09:20 | 10-03-2022 09:25 |
| 26 | ShiftCC1 | OutDateTime | 01-03-2022 18:10 | 02-03-2022 18:15 | 03-03-2022 18:05 | 04-03-2022 18:10 | 05-03-2022 18:13 | 06-03-2022 18:14 | 07-03-2022 18:16 | 08-03-2022 18:30 | 09-03-2022 18:20 | 10-03-2022 18:25 |
| 26 | ShiftCC1 | OverTimeHours | 1 | 2 | 2 | 1 | 2 | 3 | 2 | 1 | 2 | 3 |
CodePudding user response：
First of all, you can' t have the exactly output you want with your dataset. Either you need to give up your overtime column because after pivotting those columns automatically convert to date type or you could change datatype of your columns(I used view for that).
Also, I assumed your "AttendanceDate" is dynamic, so I used a dynamic solution.
