Attendance UNPIVOT first then Pivot for count present, absent etc in SQL

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

Share