As anyone who has ever worked with financial data within Excel knows, the ability to pivot data is an essential tool for gaining a deeper understanding of the data you are reviewing. SQL Server also offers a more powerful feature for pivoting data. The PIVOT operator, simply put, allows you to transform data from rows to columns as well as perform aggregations as the data is being transformed.

Though this concept can be a bit daunting for beginners, it’s actually pretty easy to implement assuming you understand the data you are working with.

To better illustrate this I have outlined all the necessary steps from creating the table, populating it with data, and then querying it using two different pivot queries to demonstrate various techniques that can be implemented to summarize the data.

For the purposes of this example I will be using the TSQL2012 database.

The first step will be to create the table I will be using to store the data. The script below will handle this by creating a table named TestPivot on the TSQL2012 database. For the purposes of this example I have oversimplified this table. Ideally you would not want to use reserved keywords as column names but since this post is geared towards someone who is new to using PIVOT I wanted to make the translation as simple as possible.

Create TestPivot --Create Table USE [TSQL2012] CREATE TABLE TestPivot( YEAR INT, MONTH VARCHAR(12), SALESTOTAL MONEY ) 1 2 3 4 5 6 7 --Create Table USE [ TSQL2012 ] CREATE TABLE TestPivot ( YEAR INT , MONTH VARCHAR ( 12 ) , SALESTOTAL MONEY )

This table contains three columns consisting of YEAR , MONTH and SALESTOTAL .

Once the table has been created in your database the next step will be to populate the table with data. The following script will insert 24 records into the TestPivot table.

Insert Into TestPivot --Insert Data INSERT INTO TestPivot VALUES(2015, 'January', 4343.00) INSERT INTO TestPivot VALUES(2015, 'February', 2158.00) INSERT INTO TestPivot VALUES(2015, 'March', 3131.00) INSERT INTO TestPivot VALUES(2015, 'April', 4217.00) INSERT INTO TestPivot VALUES(2015, 'May', 3897.00) INSERT INTO TestPivot VALUES(2015, 'June', 5178.00) INSERT INTO TestPivot VALUES(2015, 'July', 6218.00) INSERT INTO TestPivot VALUES(2015, 'August', 6101.00) INSERT INTO TestPivot VALUES(2015, 'September', 5838.00) INSERT INTO TestPivot VALUES(2015, 'October', 5775.00) INSERT INTO TestPivot VALUES(2015, 'November', 6128.00) INSERT INTO TestPivot VALUES(2015, 'December', 6655.00) INSERT INTO TestPivot VALUES(2016, 'January', 4190.00) INSERT INTO TestPivot VALUES(2016, 'February', 2280.00) INSERT INTO TestPivot VALUES(2016, 'March', 3217.00) INSERT INTO TestPivot VALUES(2016, 'April', 4611.00) INSERT INTO TestPivot VALUES(2016, 'May', 4112.00) INSERT INTO TestPivot VALUES(2016, 'June', 4978.00) INSERT INTO TestPivot VALUES(2016, 'July', 6188.00) INSERT INTO TestPivot VALUES(2016, 'August', 5789.00) INSERT INTO TestPivot VALUES(2016, 'September', 5916.00) INSERT INTO TestPivot VALUES(2016, 'October', 5548.00) INSERT INTO TestPivot VALUES(2016, 'November', 6128.00) INSERT INTO TestPivot VALUES(2016, 'December', 6655.00) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 --Insert Data INSERT INTO TestPivot VALUES ( 2015 , 'January' , 4343.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'February' , 2158.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'March' , 3131.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'April' , 4217.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'May' , 3897.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'June' , 5178.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'July' , 6218.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'August' , 6101.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'September' , 5838.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'October' , 5775.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'November' , 6128.00 ) INSERT INTO TestPivot VALUES ( 2015 , 'December' , 6655.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'January' , 4190.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'February' , 2280.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'March' , 3217.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'April' , 4611.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'May' , 4112.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'June' , 4978.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'July' , 6188.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'August' , 5789.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'September' , 5916.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'October' , 5548.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'November' , 6128.00 ) INSERT INTO TestPivot VALUES ( 2016 , 'December' , 6655.00 )

Now that the table has been populated with data, run a SELECT against it to verify that all the records were added.

Verify Insert --Verify Data SELECT * FROM TestPivot 1 2 --Verify Data SELECT * FROM TestPivot

The output should look something like this:

Now it is time to transform the data. In the first example I will use the PIVOT operator to create a separate column for each month and a separate row for each year to create an alternate view of the data.

The following script will accomplish this by first querying the data from the TestPivot table and then performing the PIVOT operator. There are other ways to initiate a pivot query such as using the WITH PivotData AS command, but for simplicity the following example will work just fine.

Pivot by Month --Pivot Data (by Month) SELECT * FROM ( SELECT [YEAR],[MONTH], SALESTOTAL FROM dbo.TestPivot ) tp PIVOT ( SUM(SALESTOTAL) FOR [MONTH] IN ([January],[February],[March],[April], [May],[June],[July],[August],[September], [October],[November],[December]) ) p 1 2 3 4 5 6 7 8 9 10 11 12 13 --Pivot Data (by Month) SELECT * FROM ( SELECT [ YEAR ] , [ MONTH ] , SALESTOTAL FROM dbo . TestPivot ) tp PIVOT ( SUM ( SALESTOTAL ) FOR [ MONTH ] IN ( [ January ] , [ February ] , [ March ] , [ April ] , [ May ] , [ June ] , [ July ] , [ August ] , [ September ] , [ October ] , [ November ] , [ December ] ) ) p

Within the PIVOT, the first step taken calls the SUM aggregate function to total the values by month. In this case, since the data is already totaled by month, this value doe not change. The next step specifies the rows that will be transformed into columns. In this case I will be creating a separate column for each month.

The results of running this script are shown below:

As you can see, along with the YEAR column, there is a separate column for each month and a separate row for each year. The SALESTOTAL is displayed under each corresponding month for each year.

Another example of using the PIVOT operator will demonstrate calculating the SUM of the SALESTOTAL for each year. Similar logic will be used to accomplish this task as shown in the following script.

Pivot by Year --Pivot Data (by Year) SELECT * FROM ( SELECT [YEAR],SALESTOTAL FROM dbo.TestPivot ) tp PIVOT ( SUM(SALESTOTAL) FOR [YEAR] IN ([2015],[2016]) ) p 1 2 3 4 5 6 7 8 9 10 11 --Pivot Data (by Year) SELECT * FROM ( SELECT [ YEAR ] , SALESTOTAL FROM dbo . TestPivot ) tp PIVOT ( SUM ( SALESTOTAL ) FOR [ YEAR ] IN ( [ 2015 ] , [ 2016 ] ) ) p

In this example I am querying only the YEAR and SALESTOTAL columns from the TestPivot table. I am then calling the PIVOT operator to SUM the SALESTOTAL by YEAR and then passing the year values in a similar way as to how I previously called the individual months to create individual columns.

Running this script will display the following output.

As you can see, there are now separate columns for 2015 and 2016 with the sales total for each month summed to display the total sales for each year.

The PIVOT operator is a powerful tool for transforming data within SQL without having to export it to Excel. It’s an excellent tool when creating high level financial reports to allow you to deliver the desired results directly to the user without them having to further manipulate the data.