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 |
SELECT [Group], [Apple], [Pineapple], [Banana], [Tomatoes], [Cucumbers]
FROM
(
-- (1)
SELECT [Code], [Group], [Quantity]
FROM #Food
) S
pivot
(
SUM(Quantity) -- (2)
FOR Code in ([Apple], [Pineapple], [Banana], [Tomatoes], [Cucumbers]) -- (3)
) P
| Group | Apple | Pineapple | Banana | Tomatoes | Cucumbers |
|---|---|---|---|---|---|
| Fruit | 57 | 14 | 1 | null | null |
| Vegetables | null | null | null | 8 | 3 |