for this I recommend you to use the CROSSTAB query. In SQL PIVOT table.
http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx[
^]
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[
^]
try your self. If you have any question feel free to ask.
-------------------------
Problem with your PIVOT aggregation. you can use something line
MAX([Status])
SELECT [EmpCode], [ProjectName],[1] , [2],[3],[4],[5]
FROM
(SELECT Day([AttendanceDate]) as d1, [EmpCode],[ProjectName],[AttendanceDate] , [Status]
FROM [tebs].[dbo].[View_Attendance]) p
PIVOT(
MAX([Status])
FOR d1 IN ( [1] , [2],[3],[4],[5]) ) AS pvt
ORDER BY
pvt.[EmpCode], pvt.[ProjectName];