Hi,
Please refer below SQL Script
SQL
DECLARE @Test AS TABLE(RegNo INT,FirstName VARCHAR(20),LastName VARCHAR(20),Day VARCHAR(10),Hour VARCHAR(10),Present CHAR(1))
INSERT INTO @Test VALUES
(1,'Gopi','A.','Mon','II','P'),
(2,'Pradeep','S.','Mon','II','A'),
(3,'John','B.','Tue','III','A'),
(4,'Bala','M.','Tue','III','P'),
(1,'Gopi','A.','Mon','II','P'),
(2,'Pradeep','S.','Mon','II','A'),
(3,'John','B.','Tue','III','A'),
(4,'Bala','M.','Tue','III','P'),
(1,'Gopi','A.','Mon','II','P'),
(2,'Pradeep','S.','Mon','II','P'),
(3,'John','B.','Tue','III','A'),
(4,'Bala','M.','Tue','III','P')
SELECT distinct pvtTable.RegNo,T.FirstName,pvtTable.LastName,pvtTable.[Day],pvtTable.[Hour],pvtTable.[P] as Total_Present,pvtTable.[A] Total_Absent FROM (
SELECT * FROM @Test
PIVOT (COUNT(FirstName) FOR Present IN ([P],[A])) as p
) as pvtTable
, @Test T
where T.RegNo = pvtTable.RegNo
Screenshot
