How do I return a new column for every match

Question

I have a table of the following format:

Ref	OtherRef
Fruit	Apple
Fruit	Banana
Fruit	Lemon
Vegetable	Carrot
Vegetable	Potato

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

Ref	OtherRefA	OtherRefB	OtherRefC
Fruit	Apple	Banana	Lemon
Vegetable	Carrot	Potato

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.

Answer 1

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    |           |
-------------------------------------------------