One of the frustrating things about using the SQL Server PIVOT function is that you need to explicitly name the field values that you want to be pivoted across the horizontal access of your result set. The problem with this is that this list of values could change over time as new data is added to the database. So what's the solution?
In this post I’ll demonstrate how you can create a stored proc that will take a SQL query, derive the unique values in a column and dynamically create the SQL necessary to generate PIVOT table based on these values.
Below you’ll see the code needed to create a stored procedure that takes a normal select query and automatically determines the column headings for the PIVOT statement.
Let me take you through the code:
@SQL = This is a variable that will hold a normal select statement.
@Columns = This denotes the list of columns that you want to display along the vertical axis.
@PivotCol = This is the name of the column that you want to pivot across the horizontal axis.
@Summaries = This is the aggregate function and field that you want to summaries in the pivot table e.g. SUM(SalesAmount)
@Debug = 0 = Run the script, 1 = Debug the code, in this instance the SQL that is used to create the pivoted result set is printed to the results pane in SSMS.
The first thing we have to determine is the distinct field values that exists within the column that you have chosen to be the @PivotCol. In order to do this we create a dynamic query that is placed inside a vairable @IDListSQL. The query uses the @SQL query and the XML Path function to return a concatenated list of values, we place these in square brackets just in case some of the values have invalid characters for column names i.e. they start with a number.
In order to make the code more readable I’ve used the replace function to replace place holders in the query with the values from the actual variable. I find this helps make the code more readable and maintainable going forward, there is no need for lots of string concatenation.
I then use the sp_executesql system stored proc to execute the dynamic sql and return the unique list of column values into the @IDList varaible, this list will eventually make up our horizontal axis.
Once we have a list of fields we then construct the final select statement. Again I uses the replace function to switch place holders in the dynamic sql statement with the values in the actual variables.
Once this is done we can execute the SQL and generate a pivoted data set.
Here’s an example of the proc in action.
You can see in this example that we've used a Row Construcutor to create our sample data. We demonstrated the various uses of Row Constructors in a previous blog post.