try following t-sql code block :
CREATE TABLE #TEMP
(
RID INT,
ROOMNO INT,
CUSID INT,
ARRDATE DATETIME,
DEPTDATE DATETIME,
STATUS VARCHAR(50)
)
INSERT INTO #TEMP VALUES(1,102,3,'10/13/2012','10/17/2012','GRNT')
INSERT INTO #TEMP VALUES(2,103,5,'10/14/2012','10/19/2012','GRNT')
INSERT INTO #TEMP VALUES(3,105,1,'10/11/2012','10/12/2012','GRNT')
DECLARE @RoomNo VARCHAR(max)
SELECT @RoomNo= ISNULL(@RoomNo + ',', '') + cast(ROOMNO as varchar(5))
FROM #TEMP
WHERE (ARRDATE BETWEEN '10-16-2012' AND '10-19-2012'
OR DEPTDATE BETWEEN '10-16-2012' AND '10-19-2012')
SELECT @RoomNo
DROP TABLE #TEMP
it will work for you.