Sample data download: here
Declare @tempAttendance table(EmpID int, EmpName varchar(50), edate int, ecode varchar(5)) insert into @tempAttendance SELECT empid, empname, code as 'edate', value as 'ecode' FROM [dbo].[hattendance] UNPIVOT ( value FOR code IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31] ) ) unpiv --select * from @tempAttendance SELECT empid, empname, P , A, FH, NA FROM (SELECT empid, empname, ecode FROM @tempAttendance ) ps PIVOT ( count (ecode) FOR Ecode IN ( P , A, FH, NA ) ) AS pvt order by empid
download sql file from here