How to make a pivot table (with dynamic 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.

-- 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
) P

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