SQL Server 2017

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

CREATE TABLE [dbo].[AssetRegister](
	[AssetRegisterID] [int] NOT NULL,
	[AssetID] [nvarchar](100) NULL,
	[Description] [nvarchar](max) NULL,
	[LocationRoomID] [int] NOT NULL
	);
CREATE TABLE [dbo].[LocationRoom](
	[LocationRoomID] [int] NOT NULL,
	[RoomNo] [nvarchar](max) NULL,
	[LocationDescription] [nvarchar](max) NULL
);
INSERT INTO [dbo].[AssetRegister]
           ([AssetRegisterID],[AssetID],[Description],[LocationRoomID])
VALUES   (1,'01780','DELL LAPTOP', 1),
         (2,'01781','DELL MONITOR', 1),
		 (3,'01782','DELL CPU', 1),
		 (4,'01783','DELL KEYBOARD', 1),
		 (5,'01784','HP LAPTOP', 2),
		 (6,'01785','HP MONITR', 2),
		 (7,'01786','HP CPU', 2),
		 (8,'01787','HP KEYBOARD', 2),
		 (9,'01788','APPLE LAPTOP AIR', 3),
		 (10,'01789','APPLE LAPTOP PRO', 3),
		 (11,'01790','APPLE MONITOR', 3),
		 (12,'01791','APPLE CPU', 3),
		 (13,'01792','APPLE KEYBOARD', 3),
		 (14,'01793','APPLE IPAD', 3),
		 (15,'01794','APPLE WATCH', 3),
		 (16,'01795','APPLE PHPNE', 3),
		 (17,'01796','IBM LAPTOP', 4),
		 (18,'01797','IBM LAPTOP 2', 4),
		 (19,'01798','IBM SERVER', 4);
19 rows affected

INSERT INTO [dbo].[LocationRoom]
           ([LocationRoomID],[RoomNo],[LocationDescription])
     VALUES
           (1,'R0001','IT Lab 1'),
		   (2,'R0002','IT Lab 2'),
		   (3,'R0003','IT Lab 3'),
		   (4,'R0004','IT Lab 4');
4 rows affected

SELECT  '('+reverse(stuff(reverse(( 
		SELECT A.[AssetID] +',' 
		FROM [dbo].[AssetRegister] A
		WHERE A.LocationRoomID = AR.LocationRoomID
		FOR XML PATH(''))), 1, 2,''))+')'
		, 
		'('+reverse(stuff(reverse(( 
		SELECT A.[Description] +',' 
		FROM [dbo].[AssetRegister] A
		WHERE A.LocationRoomID = AR.LocationRoomID
		FOR XML PATH(''))), 1, 2,''))+')'
      ,LR.[RoomNo]
      ,COUNT(*)
FROM [dbo].[AssetRegister] AR
INNER JOIN [dbo].[LocationRoom] LR
on AR.LocationRoomID = LR.LocationRoomID
GROUP BY LR.[RoomNo],AR.LocationRoomID;


(No column name)
(No column name)
RoomNo
(No column name)


(01780,01781,01782,0178)
(DELL LAPTOP,DELL MONITOR,DELL CPU,DELL KEYBOAR)
R0001
4

(01784,01785,01786,0178)
(HP LAPTOP,HP MONITR,HP CPU,HP KEYBOAR)
R0002
4

(01788,01789,01790,01791,01792,01793,01794,0179)
(APPLE LAPTOP AIR,APPLE LAPTOP PRO,APPLE MONITOR,APPLE CPU,APPLE KEYBOARD,APPLE IPAD,APPLE WATCH,APPLE PHPN)
R0003
8

(01796,01797,0179)
(IBM LAPTOP,IBM LAPTOP 2,IBM SERVE)
R0004
3

(No column name)	(No column name)	RoomNo	(No column name)
(01780,01781,01782,0178)	(DELL LAPTOP,DELL MONITOR,DELL CPU,DELL KEYBOAR)	R0001	4
(01784,01785,01786,0178)	(HP LAPTOP,HP MONITR,HP CPU,HP KEYBOAR)	R0002	4
(01788,01789,01790,01791,01792,01793,01794,0179)	(APPLE LAPTOP AIR,APPLE LAPTOP PRO,APPLE MONITOR,APPLE CPU,APPLE KEYBOARD,APPLE IPAD,APPLE WATCH,APPLE PHPN)	R0003	8
(01796,01797,0179)	(IBM LAPTOP,IBM LAPTOP 2,IBM SERVE)	R0004	3