compile sql server online

Language: Layout:

--Title of this code
--Sql Server 2012 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

CREATE TABLE test1  (ingredient  VARCHAR(30),   product VARCHAR(30))

INSERT INTO test1 values
('Ing1','AAA'),
('Ing1','BBB'),
('Ing2','AAA'),
('Ing2','BBB');

declare @Finalcollist varchar(max), @collist varchar(max)
declare @sql varchar(max)
 
select @Finalcollist = COALESCE(@Finalcollist + ', ','') 
                      + QUOTENAME(ingredient) +' AS '
                      + Replace (QUOTENAME(ingredient),'Ing','Ingredient')
from 
( SELECT DISTINCT ingredient FROM test1) T (ingredient)

select @collist = COALESCE(@collist + ', ','') 
                      + QUOTENAME(ingredient)                     
from 
( SELECT DISTINCT ingredient FROM test1) T (ingredient)
 
select @Finalcollist,@collist -- This givesdynamic column list
 
-- Now setting this @Finalcollist variable in the Dynamic SQL.
set @sql = '
select product, ' + @Finalcollist + '
from 
(select product,ingredient from test1
 )p
PIVOT(MAX (ingredient) FOR ingredient IN ( ' + @collist + ' )
) AS pvt
ORDER BY product'
 
print @sql
 
exec (@sql)

View schema
Execution time: 0.02 sec, rows selected: 9, rows affected: 4, absolute service time: 0.11 sec