title: How to select a specific number of rows and convert them into named columns
body: <p>I am hanging on this problem for hours.</p>
  <p>A very simple query:</p>
  <pre><code>SELECT 
   RoomId,
   WaterMeterNumber
  FROM Watermeter
  WHERE RoomId = GR.RoomId 
  </code></pre>
  <p>SQL Result:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>RoomId</th>
  <th>WaterMeterNumber</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF</td>
  <td>SZ12800491</td>
  </tr>
  <tr>
  <td>95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF</td>
  <td>3375791</td>
  </tr>
  <tr>
  <td>95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF</td>
  <td>45332</td>
  </tr>
  <tr>
  <td>95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF</td>
  <td>SK9649</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And I want the query result to be like that:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>RoomId</th>
  <th>Number1</th>
  <th>Number2</th>
  <th>Number3</th>
  <th>Number4</th>
  <th>Number5</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF</td>
  <td>SZ12800491</td>
  <td>3375791</td>
  <td>45332</td>
  <td>SK9649</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I always want to show the numbers of the first five watermeters of a room.
  So the columns need to have a static name.</p>
  <p>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.</p>
  <pre><code>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
  </code></pre>
  <p>This query would work but how I said the RoomId needs to be dynamic.</p>
  <p>I want it that way, so I can use it in another main query.</p>
  <pre><code>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
  </code></pre>
  <p>Is there a way to achieve this with a pivot function or smth like that?</p>
  <p>Thank you in advance.</p>