select 
	NameOfEvent
	,[1] as Sun
	,[2] as Mon
	,[3] as Tue
	,[4] as Wed
	,[5] as Thu
	,[6] as Fri
	,[7] as Sat
from
(select
	NameOfEvent -- each distinct event will be shown in a row
	,count(id) as nof_events --for each event you will count frequencies
	,day(MyDate) as day_of_week --day of the week

from MyTable
where 
	MyDate between '2010/08/01' and '2010/08/09'
group by day(MyDate), NameOfEvent

) as p pivot
( sum(nof_events) for day_of_week in ([1],[2],[3],[4],[5],[6],[7])
) as pivottbl

--CREATE TABLE
create table dabram
(id int identity(1,1)
,DateOfEvent datetime
,event nvarchar(50)
)

--GET SOME DATA
insert into dabram
		  select '2010-06-01','Fix Car'
union all select '2010-06-02','Walk the dog'
union all select '2010-06-03','Take cat to vet'
union all select '2010-06-04','Take dog to vet'
union all select '2010-06-05','Feed fish'
union all select '2010-06-06','Fix water leak'
union all select '2010-06-07','Do this'
union all select '2010-06-08','Do that'
union all select '2010-06-09','Do whatever'
union all select '2010-06-10','Do nothing'
union all select '2010-06-11','Workday 11'
union all select '2010-06-12','Workday 12'
union all select '2010-06-13','Workday 13'
union all select '2010-06-14','Workday 14'
union all select '2010-06-15','Workday 15'
--(15 row(s) affected)

--ORIGINAL DATA
SELECT *,datepart(week,DateOfEvent) as wk from dabram

--SOLUTION WITH PIVOT
select 
	 wk
	,[1] as Sun
	,[2] as Mon
	,[3] as Tue
	,[4] as Wed
	,[5] as Thu
	,[6] as Fri
	,[7] as Sat
from
(select
	 [Event] 
	,datepart(week,DateOfEvent) as wk -- week number
	,datepart(weekday,DateOfEvent) as day_of_week --day of the week

from dabram

) as p pivot
--use either min(event) or max(event) for distincting the string
( min([event]) for day_of_week in ([1],[2],[3],[4],[5],[6],[7])
) as pivottbl