Pivoting a table when you don’t know how many columns you need with dynamic SQL

Here’s the situation, you’ve got a column in a table which you want to pivot on but you don’t have any control over the distinct number of values that can be in that column. It could be 3 or it could be 20. However, you still need to be able to pivot the whole table to display the information to the user. How do you do this?

The answer here lies in the way you can use string variables as code in dynamic SQL, and there are two parts of the pivot code which have become variables in the situation outlined above:

Therefore, to create this pivot when we have a variable number of values in our PivotSource column we need to populate two strings with the distinct values of the PivotSource Column — Variable Block #1 and Variable Block #2.

To do this we will use the method I explained in an earlier post. For Variable Block #1 the code looks like:

The stuff command here removes the comma at the beginning so that we have a comma-separated list of values and no trailing comma.

The code for Variable block #2 is similar but formatted slightly differently.

Finally we need to construct our Dynamic SQL statement that we need to execute:

And there you have it, a Pivot statement that can pivot a table if you don’t know how many variables will be in the column you want to pivot on.

-J

Comments are closed.

%d bloggers like this: