I have the following data:
| Employee | Date | Amount |
|---|---|---|
| Emp 1 | 01/01/2023 | 1.50 |
| Emp 1 | 01/01/2023 | 7.5 LB |
| Emp 1 | 02/01/2023 | 0.00 |
| Emp 1 | 02/01/2023 | 1.5 LB |
| Emp 1 | 03/01/2023 | 7.5 LB |
I would like to pivot the table such that for a day where there's multiple records, each record should be on its own row, as follow:
| Employee | 01/01/2023 | 02/01/2023 | 03/01/2023 |
|---|---|---|---|
| Emp 1 | 1.50 | 0.00 | |
| Emp 1 | 7.5 LB | 1.5LB | 7.5 LB |
Before I had multiple values per day, my query was as follows:
Select Employee, Date, case when Type = 'A' then Amount else Amount + ' ' + 'LB' end as Amount from Table1
Pivot (max(Amount) for Dates in ([1],[2],[3],....[30],[31]) as PivotTable
Can you please assist on how I can achieve this?