Try it:
1) Static version:
DECLARE @rooms TABLE (Floorno VARCHAR(30),RoomNo INT)
INSERT INTO @rooms (Floorno ,RoomNo)
SELECT 'First_Floor', 101
UNION ALL SELECT 'First_Floor', 102
UNION ALL SELECT 'Second_Floor', 201
UNION ALL SELECT 'Second_Floor', 202
UNION ALL SELECT 'Third_Floor', 301
UNION ALL SELECT 'Third_Floor', 302
SELECT Floorno, [1], [2]
FROM (
SELECT Floorno ,RoomNo, ROW_NUMBER() OVER (PARTITION BY Floorno ORDER BY Floorno, RoomNo) AS RoomID
FROM @rooms
) AS DT
PIVOT(MAX(RoomNo) FOR RoomID IN([1],[2])) AS PT
Result:
Floorno 1 2
----------------------------
First_Floor 101 102
Second_Floor 201 202
Third_Floor 301 302
2) Dynamic version
CREATE TABLE #rooms (Floorno VARCHAR(30),RoomNo INT)
INSERT INTO #rooms (Floorno ,RoomNo)
SELECT 'First_Floor', 101
UNION ALL SELECT 'First_Floor', 102
UNION ALL SELECT 'Second_Floor', 201
UNION ALL SELECT 'Second_Floor', 202
UNION ALL SELECT 'Third_Floor', 301
UNION ALL SELECT 'Third_Floor', 302
UNION ALL SELECT 'Third_Floor', 303
DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(NVARCHAR(10),O.[RoomID])
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Floorno ORDER BY Floorno, RoomNo) AS RoomID
FROM #rooms
) AS O
ORDER BY '],[' + CONVERT(NVARCHAR(10),O.[RoomID])
FOR XML PATH('')),1,2,'') + ']'
SET @dt = N'SELECT Floorno ,RoomNo, ROW_NUMBER() OVER (PARTITION BY Floorno ORDER BY Floorno, RoomNo) AS RoomID
FROM #rooms'
SET @pt = N'SELECT Floorno, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT(MAX(RoomNo) FOR RoomID IN(' + @cols + ')) AS PT'
EXEC(@pt)
DROP TABLE #rooms
Result:
Floorno 1 2 3
------------------------------------
First_Floor 101 102 NULL
Second_Floor 201 202 NULL
Third_Floor 301 302 303