SELECT Table1.houseid, Table1.flatnumber, Table1.roomnumber, Max(Table1.payscale) AS [Max Of payscale]
FROM Table1
GROUP BY Table1.houseid, Table1.flatnumber, Table1.roomnumber;
But It won't return tenant number. I'm trying to get that too. I'll let you know if I could get it!
You can use filters or sort by order to group rows vertically as shown in this gif - http://i.imgur.com/rZWI5fA.gif
But I'm not sure if you can get all the users horizontally in the same row unless you write a custom script.
