SQL Server snippets
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 .