PIVOT Examples in SQL Server

By using the PIVOT operator (introduced in SQL Server 2005), you can rotate values from a column into column names and display aggregated data.

First, we will create a table and insert some test data:

CREATE TABLE SalesTest (Country char(2), Year int, SalesValue int)
GO
INSERT INTO SalesTest VALUES ('US', 2012, 45)
INSERT INTO SalesTest VALUES ('US', 2013, 30)
INSERT INTO SalesTest VALUES ('US', 2011, 96)
INSERT INTO SalesTest VALUES ('FR', 2010, 30)
INSERT INTO SalesTest VALUES ('GB', 2010, 22)
INSERT INTO SalesTest VALUES ('GB', 2011, 81)
INSERT INTO SalesTest VALUES ('US', 2012, 45)
INSERT INTO SalesTest VALUES ('FR', 2010, 30)
INSERT INTO SalesTest VALUES ('US', 2012, 65)
INSERT INTO SalesTest VALUES ('GB', 2011, 99)
INSERT INTO SalesTest VALUES ('US', 2012, 45)
INSERT INTO SalesTest VALUES ('GB', 2012, 30)
INSERT INTO SalesTest VALUES ('US', 2010, 72)
INSERT INTO SalesTest VALUES ('GB', 2013, 30)
INSERT INTO SalesTest VALUES ('FR', 2012, 96)
INSERT INTO SalesTest VALUES ('FR', 2012, 65)
INSERT INTO SalesTest VALUES ('US', 2013, 11)
INSERT INTO SalesTest VALUES ('FR', 2011, 93)
INSERT INTO SalesTest VALUES ('GB', 2010, 96)
INSERT INTO SalesTest VALUES ('FR', 2013, 72)
INSERT INTO SalesTest VALUES ('US', 2011, 96)
INSERT INTO SalesTest VALUES ('GB', 2013, 66)
INSERT INTO SalesTest VALUES ('US', 2012, 30)
GO

This is what the data in the table looks like:

SELECT *
FROM SalesTest

pivot_example_table_select

If you want to create a report that lists sum of all sales ordered by year for each country you can use this code:

SELECT *
FROM
(SELECT [Country], [Year], [SalesValue] FROM SalesTest) st
PIVOT (SUM (SalesValue)
FOR Country IN ([US], [GB], [FR])) as TotalSales

pivot_example_table_order_by_year

If you want to create a report that lists sum of all sales ordered by country for each year you can use this code:

SELECT *
FROM
(SELECT [Country], [Year], [SalesValue] FROM SalesTest) st
PIVOT (SUM (SalesValue)
FOR [Year] IN ([2010], [2011], [2012], [2013])) as TotalSales

pivot_example_table_order_by_country

Related blog post:
UNPIVOT Examples in SQL Server