clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36252 in the last week).

CREATE TABLE #Food( [Code] [nvarchar](50) NOT NULL, [Group] [nvarchar](100) NULL, [Quantity] [int] NOT NULL, [Color] [nvarchar](50) NULL, ) ON [PRIMARY];
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


-- 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 14
Vegetables 3 8
 hidden batch(es)