How to make a pivot table (with fixed columns) in SQL Server

Example 1

Starting from the #Food temporary table

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');

you can select a pivot table with the following query.

SELECT [Group], [Apple], [Pineapple], [Banana], [Tomatoes], [Cucumbers]
FROM
(
   /* (1) SELECT here all the useful columns from the original table */
   SELECT [Code], [Group], [Quantity]
   FROM #Food
) S
pivot
(
  SUM(Quantity) /* (2) list here the columns to aggregate with SUM/MAX/MIN/AVG/... functions */
  FOR Code in ([Apple], [Pineapple], [Banana], [Tomatoes], [Cucumbers])
  /* (3) list here all the columns on which you want to pivot the original table.
      Please note that on this website you can find an example with dynamic columns.*/
) P

You can find an interactive version of this example following this link .