select *
from
(
select dense_rank() OVER (ORDER BY card_no) as [Sl. No.], card_no as [Card Number],employee_fname as [Employee Name],
DeptName as [Department] ,Designation,
case when Att_PunchRecDate is not null then 'P' when TWeekDay='Sun' then 'W' WHEN CAST(TDate AS DATE) >= CAST(GETDATE() AS DATE) then '' else 'A' end as val,TDateName,'' as Remark ,COUNT(CASE when Att_PunchRecDate is not null then 1 ELSE 0 END) as [Total Present] from #AttRec
) src
pivot
(
max(val)
for TDateName in ( [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])
) piv;