SQL PIVOT

Transact-SQL (TSQL) Quick Tips – SQL PIVOT

Here is a simple example of how SQL PIVOT works in TSQL.  You can copy this code into your Query Window and EXECUTE as-is.  The code includes a #temp table with data to illustrate the functionality.  You can modify to suit your needs.

The PIVOT functionality is used to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

In short, it is a nice way to take a column from one table output and present the unique values as rows in a different table, with aggregations on the column values.  If you have used Excel, this is essentially creating a Pivot Table.

Say you have the following table with students grade results for a given semester:

SQL PIVOT

However, you were looking to display a simple table like the following, showing average grade for each student, for a given subject over the course of all 4 semesters:

SQL PIVOT

Setup

This is accomplished using SQL PIVOT:

SQL PIVOT – AVG

And if you wanted to find the AVG grade for each semester, simply change the following section:

Which results in:

SQL PIVOT

 

If you would like to post any additional examples, please add them to the comments below.

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

1 thought on “SQL PIVOT

  1. Great post. Helped me figure out a request that’s been nagging me for a few days. Wasn’t familiar with the PIVOT function, but caught wind of it and this post got me through it. Thanks again!

Leave a Comment