Check with below query.
CREATE TABLE #tblAttendance(AdmissionNo VARCHAR(5),Class VARCHAR(5),Date DATE,Remarks VARCHAR(50))
INSERT INTO #tblAttendance VALUES('R-1','ONE','10-03-2021','Present')
INSERT INTO #tblAttendance VALUES('R-2','ONE','10-03-2021','Absent')
INSERT INTO #tblAttendance VALUES('R-3','ONE','10-03-2021','Leave')
INSERT INTO #tblAttendance VALUES('R-4','ONE','10-03-2021','Present')
INSERT INTO #tblAttendance VALUES('R-5','TWO','10-03-2021','Absent')
INSERT INTO #tblAttendance VALUES('R-6','TWO','10-03-2021','Leave')
INSERT INTO #tblAttendance VALUES('R-7','TWO','10-03-2021','Present')
;WITH CTE AS
(SELECT Class,[Absent]+[Leave]+[Present] Total,[Absent],[Leave],[Present]
FROM (SELECT AdmissionNo,Class,Remarks FROM #tblAttendance)t
PIVOT (COUNT(AdmissionNo) FOR Remarks IN ([Absent],[Leave],[Present])) piv)
SELECT Class,Total,[Absent],[Leave],[Present]
,(SELECT STUFF(
(SELECT ', ' + AdmissionNo
FROM #tblAttendance ta
WHERE ta.Class = CTE.Class
AND ta.Remarks IN('Absent','Leave')
FOR XML PATH('')
), 1, 1, '')
) AS 'Name of Absent / Leave Student'
FROM CTE
UNION ALL
SELECT 'Total',SUM(Total),SUM([Absent]),SUM([Leave]),SUM([Present]),''
FROM CTE
DROP TABLE #tblAttendance
Output
| Class |
Total |
Absent |
Leave |
Present |
Name of Absent / Leave Student |
| ONE |
4 |
1 |
1 |
2 |
R-2, R-3 |
| TWO |
3 |
1 |
1 |
1 |
R-5, R-6 |
| Total |
7 |
2 |
2 |
3 |
|
