SELECT *

FROM (VALUES

('1','RM100001','2','Degree 2','6','9000000.00','1','Free','1','Floor 001'),

('2','RM100002','1','Degree 1','4','6000000.00','1','Free','1','Floor 001'),

('3','RM100003','2','Degree 2','3','4500000.00','1','Free','1','Floor 001'),

('4','RM100004','3','Degree 3','5','4800000.00','1','Free','1','Floor 001'),

('5','RM100005','1','Degree 1','3','4700000.00','1','Free','1','Floor 001'),

('6','RM100006','1','Degree 1','6','7500000.00','1','Free','1','Floor 001'),

('7','RM100007','1','Degree 1','5','7000000.00','1','Free','1','Floor 001'),

('8','RM100008','1','Degree 1','2','2500000.00','1','Free','1','Floor 001'),

('9','RM100009','3','Degree 3','3','3500000.00','1','Free','1','Floor 001'),

('10','RM100010','3','Degree 3','8','8000000.00','1','Free','1','Floor 001'),

('11','RM100011','2','Degree 2','5','6500000.00','1','Free','2','Floor 002'))V(RoomID,RoomNumber,RType,RDesc,Beds,Price,RoomStatuse,RDesc2,FloorID,RoomFloorTitle)), --Having multiple columns with the same name is a design flaw.

--I have named the second Rdesc as RDesc2, but this is always flawed.

--Fix your design, normalise your data.

RNs AS(

SELECT RoomNumber,

RoomFloorTitle,

ROW_NUMBER() OVER (PARTITION BY RoomFloorTitle ORDER BY RoomNumber) AS RN --ORDER BY should be your ID/always ascending column

FROM YourTable)

SELECT MAX(CASE RoomFloorTitle WHEN 'Floor 001' THEN RoomNumber END) AS Floor001,

MAX(CASE RoomFloorTitle WHEN 'Floor 002' THEN RoomNumber END) AS Floor002

FROM RNs

GROUP BY RN;