Hi smile,
Use below query.
SQL
CREATE TABLE #Test (AdmissionNo VARCHAR(15),ADate VARCHAR(10),Remarks VARCHAR(10))
INSERT INTO #Test VALUES('ABC','20.11.2020','Present')
INSERT INTO #Test VALUES('MNO','20.11.2020','Absent')
INSERT INTO #Test VALUES('SKY','20.11.2020','Absent')
INSERT INTO #Test VALUES('XYZ','20.11.2020','Present')
SELECT
(SELECT COUNT(*) FROM #Test WHERE Remarks = 'Present') 'Present',
(SELECT COUNT(*) FROM #Test WHERE Remarks = 'Absent') 'Absent',
STUFF(( SELECT ',' + AdmissionNo
FROM #Test
WHERE Remarks = 'Absent'
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') 'Absent Name'
Output
| Present |
Absent |
Absent Name |
| 2 |
2 |
MNO, SKY |
