Dynamic Columns in T-SQL

Tweet

In one of my projects I had to create a SQL query which has dynamic columns. The reason why was related to the fact that I needed to create a pivot table (and that is quiet easy) with dynamic columns. The challange was tricky for me at the time, but when I understood the basics behind it I started to use the method in different ways... and that solved a lot of troubles.
In the web application we are talking about, the user is able to insert and update a table containing the columns, so the pivot table needs to reflect those changes.

By the way, the solution I am explaining is commonly found on the web, so this is not really new to experienced sql programmer.

The trick is to create a stored procedure which first gets the columns, stores them in a variable and then execute the actual pivot query. Let's see it!

DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(tablefield as varchar) + ']', '[' + cast(tablefield as varchar)+ ']')
FROM table
GROUP BY tablefield

In this way we store the columns in a variable (@columns). Now it is easy to use the variable in our actual query like this:

DECLARE @query VARCHAR(8000)
SET @query = 'SELECT ' + @columns +' FROM table
PIVOT
(Aggregate Function(Measure Column)
FOR Pivot Columns IN (' + @columns + ') AS pivTable'
EXECUTE(@query)

And that's it. If you look closely to the query you will find quite easy to apply it to different situations as I did. Infact I used the same concept to create dynamic where-clause, but that's another story...