Stack Exchange
How do I title columns as a variable in SQL?
new to SQL and need some help. I'm trying to use a variable as a column title and iterate sums over a certain range that changes. Here's the code below.
DECLARE @startmonth AS INT
DECLARE @endmonth AS INT
DECLARE @earnedpremiumyear AS INT
SET @startmonth = 200501
SET @endmonth = 200512
SET @earnedpremiumyear = 2005

WHILE @startmonth <= 201301
BEGIN
    SELECT SUM(earnedpremium) AS @earnedpremiumyear
    FROM dbo.Database
    WHERE accountingmonth BETWEEN @startmonth AND @endmonth
    AND earnedendingmonth BETWEEN @startmonth AND @endmonth
    SET @startmonth = @startmonth + 100
    SET @endmonth = @endmonth + 100
    SET @earnedpremiumyear = @earnedpremiumyear + 1
END

I want it so that the title of the SUM is the earned premium year, starting at 2005 and going up to 2013. When I run this code, I get this error:

Incorrect syntax near '@earnedpremiumyear'

so I'm definitely calling the variable wrong. Can anyone help?
Also, how do I make it so that instead of creating a table for each year total, the sums are all on one table in one column or row?
Thanks!

First, +1 for your detailed question and sample code.  Thanks to that, we can see your general skill level and goals.  Second, creating dynamic names for columns as you are asking is generally not a good idea and defeats some of the best advantages of a relational database.  It becomes very difficult to feed the output into tables or other systems. A better approach is to make a 2nd column called 'year' and calculate your year there.  I recommend AHiggins's suggestion below.

Can you try simplifying it to the following?
SELECT 
    LEFT(CAST(AccountingMonth AS VARCHAR(4)),4) AS AccountingYear,
    SUM(EarnedPremium) AS TotalEarnedPremium
FROM Database.dbo.Table 
WHERE 
    AccountingMonth BETWEEN 200501 AND 201301 AND 
    EarnedEndingMonth BETWEEN 200501 AND 201301 
GROUP BY LEFT(CAST(AccountingMonth AS VARCHAR(4)),4) 


Oh my God you are a genius. I literally started using SQL at my work last week, so I still don't know a lot of the syntax and stuff, like I've never used CAST before. The result is literally what I imagined in my head. AND you did it in a simpler fashion. I'm not quite sure how it works yet, but I'll dissect it and hopefully learn how it did what it did. Thank you!

Very happy to help out!  There are some good tutorials on things like GROUP BY out there, and I hope you're able to find them helpful!

The only way I can think of doing it is through writing a dynamic SQL query:
DECLARE @startmonth AS INT
DECLARE @endmonth AS INT
DECLARE @earnedpremiumyear AS INT
SET @startmonth = 200501
SET @endmonth = 200512
SET @earnedpremiumyear = 2005
DECLARE @sql VARCHAR(MAX) = '';


WHILE @startmonth <= 201301
    BEGIN
    SELECT @sql = 'SUM(earnedpremium) AS ' + @earnedpremiumyear
    + 'FROM dbo.Database
    WHERE accountingmonth BETWEEN' + @startmonth + ' AND ' + @endmonth
    ...
    EXEC(@sql)
END

That can get pretty ugly though.

Some thing lyk this..
DECLARE @f   VARCHAR(10)='fff',
        @sql NVARCHAR(max)

SET @sql ='select 1 as ' + @f 

EXEC Sp_executesql
  @sql 

