Microsoft Gold Azure and Dynamics CRM Partner | Salesforce Experts

Occasionally I get questions about how to create a T-SQL result set with dynamic column names.  Similarly I see people with issues creating temporary tables with dynamic column names.  While it’s easy to create an entirely dynamic SQL statement that can display the dynamic column names, those statements become difficult to read and maintain.  A more practical approach is to create a temporary table and then dynamically change it’s columns before inserting into the temp table.

While you cannot dynamically create a temp table and then use that temp table outside of the scope of the dynamic execution, there is a trick you can do to work around this issue.  You can simply create a static temp table and then dynamically change it’s columns.  This includes adding and removing columns dynamically.

Below is sample T-SQL to illustrate this trick.

The T-SQL code above will return a result set with four columns where the three MONEY columns have column names named after the current month and the two prior months.

Capture2

I also want to note that dynamic column names are not generally a best practice for programming solutions.  For application solutions it’s better to handle the display of the columns within the user interface than from within the SQL.  But for ad hoc SQL statements, this trick can be  helpful.