You only need a few minor changes to your query. Instead of
1 AS CntCol
use
COUNT(*) AS CntCol
There is a bug in your SQL, some missing brackets and you need to give the sub-query an alias e.g.
...,[class 5] where Month...
should be
...,[class 5])) as src where Month...
The derived column Date is already a
DATE
so there is no need for the conversion in the Where clause. Also if you use SQL reserved words as column names you should surround them with
[ ]
i.e.
Month(CONVERT(smalldatetime,Date,103))
becomes
Month([Date])
I created some sample data on what I guessed were your table structures and with this query
select * from (
select CAST(dateOfadmission AS DATE) [Date],
WardNo,gender, count(*) as CntCol
from ipd
group by gender,dateOfAdmission,wardNo
) as tr
PIVOT(sum(CntCol) for wardNo in ([class ii],[class 2],[class 3],[class iv],[class 5])) as src
where Month([Date])=4 order by Date
I got results that look like
Date Gender class ii class 2 class 3 class iv class5
2016-04-01 F 3 2 3 2 5
2016-04-01 M 4 4 4 4 2
2016-04-02 F 3 2 3 2 5
2016-04-02 M 4 4 4 4 2