Dynamic date in column name

Question

is there a way make a column name to show the date as the name. I was thinking this for my yearly comparisons with using a function like YEAR(getdate())

Answer 1

Hi,@Jonathan Brotto
Welcome to Microsoft T-SQL Q&A Forum!
As you mentioned, you want to perform ‘yearly comparisons’, so if there is a column in the table that contains the value of date, maybe you can have a try on dynamic PIVOT.
I have a sample below, please check.

CREATE TABLE #TEST(ID INT,  
                   AMOUNT INT,  
				   Test_Date DATETIME  
				  )  
INSERT INTO #TEST VALUES  
(01,89,'2019.10.01'),  
(02,45,'2019.12.13'),  
(01,23,'2020.11.01'),  
(02,45,'2020.12.01'),  
(01,78,'2021.09.02'),  
(02,86,'2021.09.24')  
  
DECLARE @sql_str VARCHAR(8000)  
DECLARE @sql_col VARCHAR(8000)  
--Get the possible column names from the row data  
SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(YEAR(Test_Date))   
FROM #TEST   
GROUP BY YEAR(Test_Date)    
--PRINT @sql_col  
  
SET @sql_str = '  
SELECT * FROM (  
    SELECT ID,YEAR(Test_Date)AS TestYear,AMOUNT FROM #TEST) S   
	PIVOT (MAX(AMOUNT) FOR TestYear IN ( '+ @sql_col +') ) AS P   
ORDER BY P.ID'  
--PRINT (@sql_str)  
EXEC (@sql_str)  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If dynamic queries sound complicated, then maybe adjust your current query to output the results to a temporary table, then rename the column:

-- The existing query that includes 'OUTPUT':

select * OUTPUT #temp1 from SomeTables

-- Rename 'old_name' column:

declare @new_name varchar(max) = YEAR(getdate())
exec tempdb.sys.sp_rename 'tempdb.dbo.#temp1.old_name', @new_name, 'COLUMN'

select * from #temp1

Answer 3

Column names must be defined at design time, not run time.

You really do not want random column names based on year. You want a COLUMN indicating the year.

Answer 4

There is actually a way to do this without dynamic SQL. Insert the data into a temp table with a fixed name for the column. Then do:

EXEC tempdb..sp_rename '#mytemp.tempname', @actualname, 'COLUMN'

Whether this actually is a good idea or nod can be disputed. It may be better to sort this out in the presentation layer.