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 |