SQL Server group by ID & dynamic Pivot/Transpose attributes

Question

I have seen a Dynamic SQL answer that was VERY similar to my problem here, but I couldn't wrap my head around the small change that would get me to the finish line.

I am trying to illustrate all of the stores you will need to travel to in order to complete a recipe, given a table that has a list of suppliers for each ingredient.

Current Ingredient Table:

RecipeId    Supplier
1           Store A
1           Store B
2           Store A
3           Store B
3           Store C
3           Store D

Desired Ingredient Table (after group & pivot):

RecipeId    Supplier 1      Supplier 2      Supplier 3
1           Store A         Store B         NULL
2           Store A         NULL            NULL
3           Store B         Store C         Store D

Any one recipe can have zero to infinite suppliers (I can cap it at 10 if I absolutely need to) This is part of a larger query where I hope to ULTIMATELY join the ingredient table back to the recipe table yielding something like:

RecipeId    Recipe Name     Supplier 1      Supplier 2      Supplier 3      Supplier N
1           Cookies         Store A         Store B         NULL            NULL
2           Cake            Store A         NULL            NULL            NULL
3           Pie             Store B         Store C         Store D         NULL

Answer 1

Simple query for N recipes is below:

See working demo

 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)