SELECT Id,Name,[Month],
SUM(CASE WHEN Attendance = 'Present' THEN 1 ELSE 0 END) AS Present,
SUM(CASE WHEN Attendance = 'Am' THEN 1 ELSE 0 END) AS AM,
SUM(CASE WHEN Attendance = 'Pm' THEN 1 ELSE 0 END) AS PM,
SUM(CASE WHEN Attendance = 'Absent' THEN 1 ELSE 0 END) AS Absent
FROM
(
SELECT Id,Name,[Month],DateVal,Attendance
FROM table t
UNPIVOT (Attendance FOR DateVal IN ([Date1],[Date2],[Date3],[Date4],[Date5]))u
)m
GROUP BY Id,Name,[Month]
if you cant determine dates before hand use below logic to generate it dynamically
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Count of Table values
Posted - 02/04/2013 : 07:06:36
