How do I return a new column for every match

I have a table of the following format:

What I'm trying to do is flatten this out so it looks like the following:

I'm having trouble due to the dynamic nature of the additional columns as the amount of OtherRef that relate to Ref varies.

Anyone able to advise how I am able to achieve this? I'm using SQL Server 2014.

A possible solution is a dynamic statement (... with slightly different column names):

-- Dynamic columns  
DECLARE @stmt nvarchar(max)
SELECT @stmt = STUFF(
   (
   SELECT CONCAT(N', OtherRef', RN)
   FROM (
      SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY Ref ORDER BY OtherRef) AS RN
      FROM Data
   ) t
   FOR XML PATH(''), TYPE
   ).value('.', 'NVARCHAR(MAX)'),
   1, 2, N''
)  
  
-- Dynamic statement 
SET @stmt = 
   N'SELECT * ' +
   N'FROM ( ' +  
   N'   SELECT ' + 
   N'      Ref, ' + 
   N'      OtherRef, ' + 
   N'      CONCAT(''OtherRef'', ROW_NUMBER() OVER (PARTITION BY Ref ORDER BY OtherRef)) AS PivotColumn ' +
   N'   FROM Data ' +
   N') t ' +
   N'PIVOT ( ' +
   N'   MAX(OtherRef) FOR PivotColumn IN (' + @stmt + N') ' +
   N') p '

-- Execution
DECLARE @err int
EXEC @err = sp_executesql @stmt
IF @err <> 0 PRINT 'Error';

Result:

-------------------------------------------------
| Ref       | OtherRef1 | OtherRef2 | OtherRef3 |
-------------------------------------------------
| Fruit     | Apple     | Banana    | Lemon     |
| Vegetable | Carrot    | Potato    |           |
-------------------------------------------------