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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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.
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.