The Pivot and Unpivot features in SQL Server are I find quite underused. For a long time I got by with SELECT, JOIN, GROUP in SQL Server and missed out on some really handy features like PIVOT, CROSS APPLY, MERGE, XML and more recently JSON. Each of these features is another tool in your toolkit that has a time and a place and if you don’t know about them then you could be missing out.
What Does Pivot Do?
Pivot essentially rotates a set so that unique data in a given field become a field of their own.
Given the following dataset
We can use a pivot to make it look like this…
The following script will create a demo table with data to try out an example on…
So in this case we want to PIVOT on this
To do this our outer query needs to list all fields we want to select e.g January.. December. We then apply the PIVOT to the source query above telling it which field and values we want to PIVOT on…
The result set will then look like this…
What About Unpivot?
Unpivot rotates in the opposite direction of Pivot so fields become data.
Lets take the reverse of before and have each month as a field. The following script will setup a test table with data
Much like the Pivot example we create our source query which in this case is…
We then wrap the source query in an outer query that specifies the fields we want and add the UNPIVOT to it…
This gives us a result set the looks similar to our pre Pivot example.
Hopefully this post gives a good overview of what these two statements do and how to use them.