Article By:

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.

IF OBJECT_ID('tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;
    
DECLARE
    @ColumnName01 VARCHAR(50)
  , @ColumnName02 VARCHAR(50)
  , @ColumnName03 VARCHAR(50)
  , @ColumnName04 VARCHAR(50);
SELECT
    @ColumnName01 = '[Sales Person]'
  , @ColumnName02 = '[' + DATENAME(MONTH, GETDATE()) + ' Sales]'
  , @ColumnName03 = '[' + DATENAME(MONTH, DATEADD(MONTH, -1, GETDATE())) + ' Sales]'
  , @ColumnName04 = '[' + DATENAME(MONTH, DATEADD(MONTH, -2, GETDATE())) + ' Sales]';
/*
 You cannot create a temp table with
 dynamic SQL because it will not be
 available outside the scope of the
 dynamic SQL call.  
*/
CREATE TABLE #T ( TempColumn INT );
/*
 But you can ALTER an existing
 temp table with Dynamic SQL.
*/
DECLARE @SQL NVARCHAR(200);
SET @SQL = 'ALTER TABLE #T ADD ';
SET @SQL += @ColumnName01 + ' VARCHAR(50) NULL, ';
SET @SQL += @ColumnName02 + ' MONEY NULL, ';
SET @SQL += @ColumnName03 + ' MONEY NULL, ';
SET @SQL += @ColumnName04 + ' MONEY NULL ';
EXEC sys.sp_executesql @SQL;
 
/*
 Optionally, you can remove any
 static columns after you've
 appended the dynamic columns.
*/
ALTER TABLE #T DROP COLUMN TempColumn;
INSERT  INTO #T
VALUES
        ( 'Eric', '120.01', '200.32', '345.00' )
 ,      ( 'Ralph', '132.03', '33.3', '341.13' ),
        ( 'David', '987.3', '30.77', '879.00' );
SELECT * FROM #T;
IF OBJECT_ID('tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

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.

Need Assistance?

Speak with an Engineer

Not sure where to start? We're here to help walk you through the process, understand your environment, and provide the guidance you need to achieve cybersecurity maturity. Get in touch today.

Get in Touch