Converting Rows To Columns Using PIVOT In SQL Server

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.

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.

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

The output should look something like this:

select_testpivot

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.

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:

select_pivot_month

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.

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.

select_pivot_year

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.