This should do it for you:
Declare @DatePeriod datetime
Set @DatePeriod = '2011-05-30'
Select ProductName,
IsNull([1],0) as 'Week 1',
IsNull([2],0) as 'Week 2',
IsNull([3],0) as 'Week 3',
IsNull([4],0) as 'Week 4',
IsNull([5], 0) as 'Week 5'
From
(
Select ProductName,
DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, InputDate), 0), InputDate) +1 as [Weeks],
Sale as 'Sale'
From dbo.YourTable
-- Only get rows where the date is the same as the DatePeriod
-- i.e DatePeriod is 30th May 2011 then only the weeks of May will be calculated
Where DatePart(Month, InputDate)= DatePart(Month, @DatePeriod)
)p
Pivot (Sum(Sale) for Weeks in ([1],[2],[3],[4],[5])) as pv
It will calculate the week number relative to the month. So instead of week 20 for the year it will be week 2. The @DatePeriod variable is used to fetch only rows relative to the month (in this example only for the month of May)
Output using my sample data:
