The problem of transposing rows into columns is one of the most common problems discussed in Transact-SQL forum. Many times the problem of creating a dynamic pivot comes into the light. One thing that many people who ask this question forget is that such transposing is much easier to perform on the client side than on the server where we need to resort to dynamic query. However, if we want to make such pivot dynamically, the important thing to understand is that writing dynamic query is only slightly more difficult than writing static query. In fact, when I am presented with the problem of dynamic pivot, I first figure out how static query should look like. Then making such query dynamically becomes rather trivial task.

I had written on the topic of dynamic pivot on multiple columns before in this blog post 

Dynamic PIVOT on multiple columns

I don't want to re-tell what I already told in that blog so this article will show another example from the most recent thread on the topic of dynamic pivot.

In that thread I presented the following solution to the problem of dynamic pivot for unknown number of columns


In this solution the first step was figuring out the static solution using ROW_NUMBER() with partition approach. This is CASE based pivot although we could have used the true PIVOT syntax here instead. CASE based pivot is easier to use if we need to transpose multiple columns. Once we knew the static pivot, we were able to easily turn it into dynamic using WHILE loop.

Just for completion, I also show the same problem solved using PIVOT syntax:


As you see, the code is very similar to the first solution, but using PIVOT syntax instead of CASE based pivot.

I hope to add more samples to this article as new opportunities present themselves.

This article participates in the TechNet Guru Contest.