Phil Factor has some fun transposing a matrix using T-SQL:
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.
That is not at all what I would have thought up; very interesting approach. I’d probably just be lazy and shell out to R Services.