SQL Group By Help Required

Question

I have a table named People in the following format:

Date | Name.

When I count the people by Grouping By Name with

Select Date, Name, count(*)
From People
Group By Date, Name;

Will give the following

Date        Name        count(*)
10          Peter       25
10          John        30
10          Mark        25
11          Peter       15
11          John        10
11          Mark        5

But I would like the following result:

Date    Peter   John    Mark
10      25      30      25
11      15      10      5

Is this possible? This is a simple example of a more complicated database. If someone helps me in solving this problem I will use the concept to implement it in my table

Thanks!

Answer 1

another option different from turbanoff's if, for some reason, you find yourself in a situation that you cant apply a group by:

Select distinct(P.Date), 
       (select count(*) from People where date=p.date and name='Peter') as Peter,
       (select count(*) from People where date=p.date and name='John') as John,
       (select count(*) from People where date=p.date and name='Mark') as Mark
From People P

Answer 2

Select Date
     , count(case when Name = 'Peter' then 1 else null end)
     , count(case when Name = 'John' then 1 else null end)
     , count(case when Name = 'Mark' then 1 else null end)
From People
Group By Date;