SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE T(
   AreaId int,
   UpdatedOn datetime,
   DeviceStatus varchar(50)
);

INSERT INTO T VALUES (1,'2018-08-08 00:00:00.000','Active');
INSERT INTO T VALUES (1,'2018-08-08 01:00:00.000','Active');
INSERT INTO T VALUES (1,'2018-08-08 02:00:00.000','Active');
INSERT INTO T VALUES (2,'2018-08-08 00:00:00.000','Inactive');
INSERT INTO T VALUES (2,'2018-08-08 01:00:00.000','Active');
INSERT INTO T VALUES (2,'2018-08-08 02:00:00.000','Active');
INSERT INTO T VALUES (3,'2018-08-08 00:00:00.000','Active');
INSERT INTO T VALUES (3,'2018-08-08 01:00:00.000','Inactive');
INSERT INTO T VALUES (3,'2018-08-08 02:00:00.000','Inactive');

SELECT t2.AreaId, coalesce(Status,'Active') Status
FROM (
SELECT distinct AreaId,DeviceStatus,
       STUFF((
         SELECT ' and ' + CONVERT(VARCHAR(5),UpdatedOn,108) + ' hrs' + ' to ' + CONVERT(VARCHAR(5),DATEADD(HOUR,1,UpdatedOn),108) + ' hrs'
         FROM T tt
         WHERE tt.AreaId = t1.AreaId and tt.DeviceStatus =  t1.DeviceStatus
         FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
       ,1,4,'') Status
  
FROM T t1
WHERE DeviceStatus = 'Inactive'
) t1 RIGHT JOIN 
(
SELECT distinct AreaId
FROM T
) t2
on t1.AreaId = t2.AreaId

AreaId	Status
1	Active
2	00:00 hrs to 01:00 hrs
3	01:00 hrs to 02:00 hrs and 02:00 hrs to 03:00 hrs