How to select a specific number of rows and convert them into named columns

I am hanging on this problem for hours.

A very simple query:

SELECT 
 RoomId,
 WaterMeterNumber
FROM Watermeter
WHERE RoomId = GR.RoomId 

SQL Result:

RoomId	WaterMeterNumber
95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF	SZ12800491
95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF	3375791
95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF	45332
95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF	SK9649

And I want the query result to be like that:

RoomId	Number1	Number2	Number3	Number4	Number5
95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF	SZ12800491	3375791	45332	SK9649

I always want to show the numbers of the first five watermeters of a room. So the columns need to have a static name.

I already tried combining five windowing functions in mssql but the problem is that I can not dynamically set the room ID inside the window query.

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY WaterMeterId ASC) AS rownumber,
    RoomId,
    WaterMeterNumber
  FROM WaterMeter
   WHERE RoomId='95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF'
                
) AS X
WHERE rownumber = 1

This query would work but how I said the RoomId needs to be dynamic.

I want it that way, so I can use it in another main query.

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY WaterMeterId ASC) AS rownumber,
    RoomId,
    WaterMeterNumber
  FROM X.WaterMeter
   WHERE X.RoomId= GR.RoomId
                
) AS X
WHERE rownumber = 1

Is there a way to achieve this with a pivot function or smth like that?

Thank you in advance.

Answer

You can use conditional aggregation:

SELECT RoomId,
       MAX(CASE WHEN seqnum = 1 THEN WaterMeterNumber END) as watermeter_1,
       MAX(CASE WHEN seqnum = 2 THEN WaterMeterNumber END) as watermeter_2,
       MAX(CASE WHEN seqnum = 3 THEN WaterMeterNumber END) as watermeter_3,
       MAX(CASE WHEN seqnum = 4 THEN WaterMeterNumber END) as watermeter_4,
       MAX(CASE WHEN seqnum = 5 THEN WaterMeterNumber END) as watermeter_5
FROM (SELECT wm.*
             ROW_NUMBER() OVER (PARTITION BY RoomId ORDER BY WaterMeterId ASC) AS seqnum,
      FROM WaterMeter wem
     ) wm
GROUP BY RoomId;

If you want this for only one RoomId, then add:

WHERE RoomId='95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF'  

to the subquery.