The question often comes up: how do you pivot data in SQL Server? In looking closer at the question, there are two similar effects that are wanted. One is the equivalent to a pivot table in Excel, the other is a matrix transposition, where a table is simply rotated, as if it were an array, so that …

… becomes …

To put it a bit more formally,

These transposes used to be done by producing a normalised version of the first table, and then creating the transpose via a simple GROUP BY clause. Then came the PIVOT and UNPIVOT clause which remains probably the simplest way of doing it. Then came a few rather scary XML-based approaches that never caught on. Now, with JSON in SQL Server 2016, 2017, there is a new, versatile, approach.

Let’s start off by creating the test data, consisting of table representing a six by six array of random integers between one and a hundred. We’ll create a type to do this, so this can be passed as a parameter to a function

Notice that we have inserted the row number. This is because tables have no intrinsic order, so we have to make it explicit.

The Unpivot/Pivot solution is fairly simple

Before the PIVOT and UNPIVOT clause arrived, it was a lot clumsier and, in SQL Server 2016, and 2017 at least, takes twice as long.

With JSON, you have a great deal more freedom and, although the code looks messy, the operation is as fast as the Unpivot/Pivot solution.

What I’m doing is simply converting the table into its JSON form, and then using this to create a table using the multi-row VALUES  syntax which paradoxically allows expressions. The expression I’m using is JSON_Value, which allows me do effectively dictate the source within the table, via that JSON Path expression, and the destination. As it is an expression, I can do all sorts of manipulation as well as a transpose.  I could, if I wanted, (in SQL 2017)provide that path parameter as a variable. This sort of technique can be used for several other reporting purposes, and it is well-worth experimenting with it because it is so versatile.

Here are two function that do the rotation. I used them to check on performance. In my own timings they returned similar times when doing 1000 transposes.

The JSON_Value and JSON_Modify now, in SQL Server 2017 and in Azure SQL Database, allow you to provide a variable as the value of path, rather than just a literal. This would provide several opportunities for producing reports.