Multiple count where in SQL?

[tonyyeb]

Hi all

Got a table that looks like this:

Timeofbooking    Dayofbooking     Name
09:00            Tue              Paul
09:00            Thur             Steve
09:30            Tue              Phil
10:00            Thur             Sally
10:15            Tue              Jim
10:15            Tue              Bob
10:15            Thur             Mark
...

I want to output three columns:

Time  Tue  Thur
09:00 1      1
09:30 1      0
10:00 0      1
10:15 2      1
...

I can do this for individual days and this is what I have at the moment is:

select timeofbooking, count(timeofbooking) from bookingstable 
where studentref <> 'NOT AVAILABLE - DO NOT CHANGE' and dayofbooking ='tue'
group by timeofbooking
order by timeofbooking

How do I get the output to show each day next to each other?

 

[Goksly]

Look into pivots - there is an inbuilt command to do it... so you'd do the count per day / time as you are currently doing and then pivot that data. You need to specify the column you want to pivot and the values (i.e. the days) and then sum up the counts.

(note: I'm on about sql server so t-sql... you'd need to specify the database you are using for actual syntax help).

 

[tonyyeb]

Sorry should have said it is SQL Server 2005. Didn't realise you could do pivots in SQL. Ill take a look. Thanks for that!