若要以固定数量的列（即每层的最大房间数）为轴心，可以联接，然后使用窗口函数和条件聚合：

select  
    building_name,
    floor_name,
    max(case when rn = 1 then room_name end) room1,
    max(case when rn = 2 then room_name end) room2,
    max(case when rn = 3 then room_name end) room3
from (
    select
        b.building_code,
        b.building_name,
        f.floor_code,
        f.floor_name,
        r.room_name,
        row_number() over(
            partition by b.building_code, f.floor_code order by r.room_code
        ) rn
    from building_master b
    inner join floor_master f
        on f.building_code = b.building_code
    inner join room_master r 
        on  r.room_floor = f.floor_code 
        and r.room_building = b.building_code
) t
group by b.building_code, b.building_name, f.floor_code, f.floor_name

我不得不对你的模式中的关系做一些猜测，你可能需要回顾一下。
您可以通过添加更多房间来按楼层处理更多房间 max() 表达到外部 select .