dynpiv, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

create table Ingredient (RecipeId int, Supplier varchar(10))
insert into Ingredient values
(1,'Store A')
,(1,'Store B')
,(2,'Store A')
,(3,'Store B')
,(3,'Store C')
,(3,'Store D')
go

declare @q varchar(max), @cols varchar(max)
set @cols
     = STUFF((
          SELECT distinct ',' + 
               QUOTENAME('Supplier '+
                         cast(row_number()  over (partition by recipeid order by supplier ) as varchar(max))
                        ) 
            FROM Ingredient
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
set @q=
'select 
  recipeid,'+ @cols +
' from
(
    select 
       recipeid,
       Supplier, 
       ''Supplier ''+
         cast(row_number()  over (partition by recipeid order by supplier ) as varchar(max))  as r
    from 
       Ingredient 
)I
pivot
( 
    max(Supplier)   
    for r in ('+@cols+')
 )piv'
    
 exec(@q)

View schema
Execution time: 0 sec, rows selected: 5, rows affected: 6, absolute service time: 0,17 sec