SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #Food(
	[Code] [nvarchar](50) NOT NULL,
	[Group] [nvarchar](100) NULL,
	[Quantity] [int] NOT NULL,
	[Color] [nvarchar](50) NULL,
) ON [PRIMARY];
INSERT INTO #Food
  ( [Code], [Group], [Quantity],[Color] )
VALUES
  ('Apple', 'Fruit', 44, 'red'), 
  ('Apple', 'Fruit', 1, 'yellow'), 
  ('Pineapple', 'Fruit', 14, 'brown'),
  ('Apple', 'Fruit', 12, 'red'), 
  ('Banana', 'Fruit', 1, 'yellow'), 
  ('Tomatoes', 'Vegetables', 8, 'red'),
  ('Cucumbers', 'Vegetables', 3, 'green');
7 rows affected

SELECT [Code], [Group], [Quantity],[Color]
FROM #Food


Code
Group
Quantity
Color


Apple
Fruit
44
red

Apple
Fruit
1
yellow

Pineapple
Fruit
14
brown

Apple
Fruit
12
red

Banana
Fruit
1
yellow

Tomatoes
Vegetables
8
red

Cucumbers
Vegetables
3
green


-- Variables
DECLARE @pvtQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

-- Column names
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Code])
FROM (SELECT DISTINCT [Code] FROM #Food) AS Code

-- Pivot query
SET @pvtQuery = 
  N'SELECT  [Group],' + @ColumnName + '
    FROM (
    -- (1)
   SELECT [Code], [Group], [Quantity]
   FROM #Food
    ) S
    PIVOT(SUM([Quantity]) 
          FOR [Code] IN (' + @ColumnName + ')) AS PVTTable'

-- Executing @pvtQuery
EXEC sp_executesql @pvtQuery


Group
Apple
Banana
Cucumbers
Pineapple
Tomatoes


Fruit
57
1
null
14
null

Vegetables
null
null
3
null
8

Code	Group	Quantity	Color
Apple	Fruit	44	red
Apple	Fruit	1	yellow
Pineapple	Fruit	14	brown
Apple	Fruit	12	red
Banana	Fruit	1	yellow
Tomatoes	Vegetables	8	red
Cucumbers	Vegetables	3	green