Rotate rows into columns with column names not coming from the row

I’ve looked at some answers but none of them seem to be applicable to me.

Basically I have this result set:

RowNo |  Id  |  OrderNo  |
  1      101       1
  2      101       10

I just want to convert this to

|  Id  |  OrderNo_0  | OrderNo_1 |
  101       1             10

I know I should probably use PIVOT. But the syntax is just not clear to me.

The order numbers are always two. To make things clearer

Answer

And if you want to use PIVOT then the following works with the data provided:

declare @Orders table (RowNo int, Id int, OrderNo int)

insert into @Orders (RowNo, Id, OrderNo)
  select 1, 101, 1 union all select 2, 101, 10

select Id, [1] OrderNo_0, [2] OrderNo_1
from (
  select RowNo, Id, OrderNo
 from @Orders
) SourceTable
pivot (
  sum(OrderNo)
  for RowNo in ([1],[2])
) as PivotTable

Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

Note: To build each row in the result set the pivot function is grouping by the columns not begin pivoted. Therefore you need an aggregate function on the column that is being pivoted. You won’t notice it in this instance because you have unique rows to start with – but if you had multiple rows with the RowNo and Id you would then find the aggregation comes into play.