SQL Pivot by Week Of date when week of dates are not Defined in the Query

SQL Pivot by Week Of date when week of dates are not Defined in the Query

The query below brings back the sum of course hours grouped by week. The
issue is, the week values can be different every time they can not be
defined in the query. Here is an example of what is brought back in the
query:
ID Category Week of Total Hours
1111554 Case Management 7/2/2012 7
1111554 Case Management 7/9/2012 7
1111554 Case Management 7/16/2012 7
1111554 Pre-GED 7/2/2012 3
1111554 Pre-GED 7/9/2012 3
1111554 Pre-GED 7/16/2012 3
QUERY
WITH cteSource(DOP_ID, Category, WeekOf, [Hours])
AS (
SELECT DOP_ID,
Category,
DATEADD(DAY, DATEDIFF(DAY, '19000101', [Date]) / 7 * 7 ,
'19000101') AS WeekOf, -- Monday
[Hours]
FROM GW_PPP.dbo.SLAM_Attendence
)
SELECT DOP_ID AS ID,
Category,
WeekOf AS [Week of],
SUM([Hours]) AS [Total Hours]
FROM cteSource
GROUP BY DOP_ID,
Category,
WeekOf
ORDER BY DOP_ID,
Category,
WeekOf;
Again, I can't figure out how to Pivot the table by not defining the weeks
in the query
SO not using something like this where the week of is defined:
pivot
(
week of in ([7/2/2012], [7/9/2012],
[7/16/2012 ])
) piv
I would also like to add a sum column by month if possible. Here is the
output I would like:
ID Category 7/2/2012 7/9/2012 7/16/2012 July 12 Total
1111554 Case Management 7 7 7 21
1111554 Pre-GED 3 3 3 12