Find sum of hours for each date worked

I have a table of timesheet entries set up like this:

id	job_id	employee_id	hours_worked	date_worked
1	1	111	8	2022-10-01
2	1	222	8	2022-10-01
3	1	222	8	2022-10-02
4	2	222	8	2022-10-03
5	2	111	8	2022-10-04
6	2	222	5	2022-10-05
7	3	111	8	2022-10-04
8	4	333	8	2022-10-07
9	4	111	3	2022-10-09

I’m trying to find the sum of hours for the first, second, third etc dates that work was done on each job

Ideally I’d like something like this:

job_id	Day1_hours	Day2_hours	Day3_hours
1	16	8	0
2	8	8	5
3	8	0	0
4	8	3	0

The trouble I’m running into is that there can be multiple employees working on each day of the job, so using a query to select the min(date_worked) greater than a subquery for min(date_worked) is sometimes giving me the same dates. There are sometimes days in between work done on a job, so I can’t just add a day to the minimum value and check hours for that date.

How can I find the sum of hours_worked for the first date_worked, then the second, third etc?

Answer

PIVOT’s are great but conditional aggregations offer a bit more flexibility

Example

Select job_id
      ,[Day1_Hours] = sum( case when DN=1 then hours_worked else 0 end)
      ,[Day2_Hours] = sum( case when DN=2 then hours_worked else 0 end)
      ,[Day3_Hours] = sum( case when DN=3 then hours_worked else 0 end)
 From ( Select *
              ,DN = dense_rank() over (partition by job_id order by date_worked)
         From  YourTable
      ) A
 Group By Job_ID